**What are 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."*

**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 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**.

## 0 comments:

Post a Comment