<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
---|

**What are Array
Formulas? See Also Alternative to
Array Formulas **

Excel Array formulas are very powerful and useful formulas that allow more complex calculations than standard formulas. The "Help" in Excel defines them as below:

*"An array formula can perform multiple calculations and then return
either a single result or multiple results. Array formulas act on two or more sets
of values known as array arguments."*

**IMPORTANT -
Before we Start**

When I first discovered array formulas may years ago, I thought I had
found the answer to ALL my spreadsheet problems. I Started using them willy nilly
**and paid the the price.**

Perhaps the number one rule with arrays is, **only use them when needed
and know when to use them**. I have seen many users using array formulas in instances
when a standard Excel formula will do the job (eg; one of the database functions).
**Too many array formulas WILL slow down recalculation, saving, opening and closing.**

I have even seen "so called" experienced users recommending them to
other Excel users looking for help on a simple formula. This is usually due to inexperience
and/or laziness. This is very irresponsible, as the person looking for help will
also find themselves using them as their first port of call. So it is important
to know when to use them and when not to. See
**Efficient Excel
Spreadsheet Design**

It is fair to say that even my examples below are really an incorrect use of array formulas, but in the interest of keeping things simple I have used them.

**Array Formula
Rules:**

Before we show some examples of array formulas it is important to know 4 fundamental rules.

- Each argument within an array
**must**have the same amount of**rows**and**columns**. - You
**must**enter an array by pushing**Ctrl+Shift+Enter**. - You
**cannot**add the {} (braces) that surround an array yourself, pushing**Ctrl+Shift+Enter**will do this for you. - You
**cannot**use an array formula on an entire**column**.

**Pet Shop Example:**

**Suppose you have 5 Columns of data each with 200 rows.**

Column A is used to keep track of the sex of each dog sold i.e. Male or Female

Column B is used to keep track of the breed of the dogs sold.

Column C is used to keep track of the age of the dogs sold.

Column D is used to keep track whether the dog is sterilized or not i.e. Yes or
No

Column E is used to keep track of the cost of the dog sold.

- To count the number of male Poodles sold:

**=SUM(($A$2:$A$200="Male")*($B$2:$B$200="Poodle"))** - To count the number of male Poodles sold
over 3 years old:

**=SUM(($A$2:$A$200="Male")*($B$2:$B$200="Poodle")*($C$2:$C$200>2))** - To get the total cost of male Spaniels sold:

**=SUM(IF($A$2:$A$200="Male",IF($B$2:$B$200="Spaniel",$E$2:$E$200,0),0))** - To find out the average age of male dogs
sold:

**=AVERAGE(IF($A$2:$A$200="Male",$C$2:$C$200))** - To find out the average cost of male dogs
sold over 2 years old:

**=AVERAGE(IF($A$2:$A$200="Male",IF($C$2:$C$200>2,$E$2:$E$200)))** - To find out the Minimum age of dogs sold
that are sterilized:

**=MIN(IF($D$2:$D$200="Yes",$C$2:$C$200))**

All the above formulas **must** be entered with **Ctrl+Shift+Enter**

TIP: If
you are having problems writing an array formula to sum your totals then use the
Conditional sum wizard, **Tool>Wizard>Conditional sum**. If you don't see it
then you will need to add it via **Tools>Add-ins>Conditional sum wizard**.

While using array formulas can be very handy they have one draw back
and that is, **too many of them within your workbook WILL slow down Excels recalculations**.
If you have read and understood the very real pit-falls to using them I highly recommend
taking the next step and **
going here **: But please keep in mind what I have said here.

**If you will need a lot of array formulas within the same workbook
consider using, Pivot
Tables the Database functions
instead. These are ideal for extracting information from tables and databases**.

**Excel
Dashboard Reports & Excel Dashboard Charts 50% Off
Become an ExcelUser Affiliate & Earn Money**

**Special! Free
Choice** of
Complete Excel Training Course **OR **
Excel Add-ins Collection
on all purchases totaling over $64.00. ALL
purchases totaling
over $150.00 **gets you BOTH! Purchases MUST be made via
this site**. Send payment proof to [email protected] 31 days after purchase
date.

**Instant Download and Money Back Guarantee on Most Software**

**
Excel Trader Package
**Technical Analysis in Excel With $139.00 of **FREE software!**

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

**Some of our more popular products are below...**