The first time I saw an array formula I was amazed at the power of it. I was working on a scheduling package and wanted to get total number of hours for each employee within a list of around 125 employees. I thought I would need to write VBA code to get the totals which was going to take some time and may have been prone to some bugs and errors. A colleague introduced me to array formulas and from then on there was very little that could not be achieved using an array formula to get results quick without writing code.
? Where are you likely to use an Array formula ?
Array formulas are used if you need to pickup aggregate information based on a list of items.
In the example below we are picking up sum of Total Costs for an item within a list.
An array formula can be identified by the {} braces at the start and the beginning of the formula.
You start of by writing your formula as you would normally do within a cell but once completed
instead of clicking the Enter key you press a key combination of Ctrl+Shift+Enter.
This adds the {} braces to your formula and lets Excel know that it is going to be working on some condition and need to return results based on some criteria.
If you edit the formula and click on Enter instead of Ctrl+Shift+Enter combination this will just create a normal formula without the {} braces and will not give you the required result giving you a #Value! error message in your cell.
As you can see in the Image below this gives you the sum of costs for the Device "MS-9200UDLS". By dragging the formula for the rest of the devices the sum of costs for each of the devices is picked up without the need for writing code using for loops or do while loops etc.
Now if you use your ingenuity you can also get the average using the same method. Try it out yourself and if you need a hint check the Image below for the solution.
A few things to keep in mind is the range that you are using to create your array formula should be a finite range and also the column that you are using for the condition and the column that you need to pick up the value from to sum up should be of the same range in the example above the range is from Row 4 to 16.
Hoping this has helped to demystify array formulas for you and help you and amaze your colleagues at work.
? Where are you likely to use an Array formula ?
Array formulas are used if you need to pickup aggregate information based on a list of items.
In the example below we are picking up sum of Total Costs for an item within a list.
An array formula can be identified by the {} braces at the start and the beginning of the formula.
You start of by writing your formula as you would normally do within a cell but once completed
instead of clicking the Enter key you press a key combination of Ctrl+Shift+Enter.
This adds the {} braces to your formula and lets Excel know that it is going to be working on some condition and need to return results based on some criteria.
Explaining the formula above.
$D$4:$D$16 = $D19
This is similar for a for each loop iterating through all the elements within a array and returning a value of true if the value in cell D19 is equal to the value in the range $D$4:$D$16. Only in this case the value returned is the corresponding value of the cell in the range $O$4:$O$16.
In the example above this would return an array of {585,0,0,0,0,0,1170,0,0,585,0,0,0} which if summed up will give us a total of 2340.
Voila the power of Array Formulas. And all without writing a single line of code.
If you edit the formula and click on Enter instead of Ctrl+Shift+Enter combination this will just create a normal formula without the {} braces and will not give you the required result giving you a #Value! error message in your cell.
As you can see in the Image below this gives you the sum of costs for the Device "MS-9200UDLS". By dragging the formula for the rest of the devices the sum of costs for each of the devices is picked up without the need for writing code using for loops or do while loops etc.
Now if you use your ingenuity you can also get the average using the same method. Try it out yourself and if you need a hint check the Image below for the solution.
A few things to keep in mind is the range that you are using to create your array formula should be a finite range and also the column that you are using for the condition and the column that you need to pick up the value from to sum up should be of the same range in the example above the range is from Row 4 to 16.
Hoping this has helped to demystify array formulas for you and help you and amaze your colleagues at work.
