Spiga

Excel Array Formulas

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: