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."
IMPORTANT - Before we Start: Videos
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 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.
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.
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.
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.
|Excel VBA Macro - Adding Values to an Excel ComboBox|
|Advanced Excel Dynamic Named Ranges|
|Excel Advanced Filter|
|Excel: Alternate Row Colors/Color Banding|
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft