Tips for Excel, Word, PowerPoint and Other Applications
Introduction to Arrays
Why It Matters to You
Arrays are a more advanced method for performing calculations on groups of data. While Arrays are similar in application to Pivot Tables, they also allow you to insert any Excel function into the argument. Like advanced SUMPRODUCT, the resulting data table is dynamic and easy to format, but the range of functions that can be applied is broader. So, while Array statements are incredibly powerful, they can be somewhat confusing, especially if you're trying to explain them to someone else.
How To
This may take a while, so grab a comfortable chair, pour yourself a cup of coffee, and prepare for a little bit of hair pulling .... here we go.
Open the sample file called array.xls. It contains a simple data set with a couple of array functions. The data set simply shows sales by Sales Rep, by Month (Jan, Feb, Mar), and by Region (East or West).
Some of the analysis we might want to do is:
- Total Sales by Sales Rep (also achievable via SUMIF)
- Total Sales by Region (also achievable via SUMIF)
- Total Sales by Month (also achievable via SUMIF)
- Total Sales by Rep and Month (also achievable via advanced SUMPRODUCT)
- Average Sales by Month (only achievable via an Array)
- Total Sales by Sales Rep where sales are > $15 (also achievable via advanced SUMPRODUCT)
Hopefully, the above example gives you an idea of what is possible through an Array and what can more easily be done through other functions. So, how do you actually write one? Let's start with an easy one to sum Sales by Sales Rep. Even though we could do this with a SUMIF, let's see how it would be done via an Array. Take a look at the table name "Total Sales by Rep". I've calculated Total Sales for Mike and Rob using two ways to write the Array.
Method 1
The first thing you might notice are the { and } brackets. These indicate an activated Array statement. Unfortunately, if you try to edit an Array statement, the brackets go away and won't return until you activate the Array ... more on this later. If you forget to activate the Array, you'll be left with a #VALUE! error.
{=SUM(IF($B$5:$B$28=B32,$E$5:$E$28))}
The first method looks very much like a SUMIF statement. In fact, if you recall, the 3 arguments for a SUMIF are, Range, Criteria, and Sum_Range. We can almost drop these right into our array to show you how it works:
{=SUM(IF(Range=Criteria,Sum_Range))}
So what's actually going on? If we work from the inside out, you can see that Excel is applying a conditional test to one range of values IF(Range=Criteria), and then for each value that meets the test, it's collecting the associated value from Sum_Range. The final piece, the SUM statement, tells Excel what to do with all of those collected values ... in this case, add them all up. However, you can use just about any function you want. You could AVERAGE, MIN, or MAX.
Before any array statement will work though, even if you've written it correctly, you must activate it by hitting CTRL+SHIFT+ENTER. That's when you'll see the brackets appear and the magic happens.
Method 2
{=SUM(($B$5:$B$28=B32)*$E$5:$E$28)}
Here's what this would look like with the SUMPRODUCT arguments dropped in:
{=SUM((Range=Criteria)*Sum_Range)}
If you read the Advanced SUMPRODUCT article, you'll notice some similarities between the two functions. This looks a lot more like our advanced SUMPRODUCT function, and it works more or less the same way. Just like Example 1, you can see that Excel is applying a conditional test to one range of values (Range=Criteria), and then for each value that meets the test, it's collecting the associated value from Sum_Range.
A Little More Advanced
Hopefully, you got those two examples to work. Now let's try something a little more difficult. We're going to average sales figures, by month. This is a case where the Array has to be written a specific way. You'll notice that cells C37 and D37, in the sample file, are written very much like the two examples above, but only one (Method 1) returns the right value.
The reason why Method 1 works and Method 2 does not, is a tricky one, but I think I can explain it. The way I read it, Method One takes all of the values that meet the Criteria and discards those that don't. Method 2, takes all of the values that meets the Criteria at face value, and converts those that don't to zeros. If you look at the results, Method 2 is generating values exactly a third of Method 1, which seems to indicate that it's calculating the average of all values instead of just the values for each month. I'll have to check into this.
What this means is that you really have to take a second to understand what you want to do, before you start writing your Array formula.
{=AVERAGE(IF($C$5:$C$28=B37,$E$5:$E$28))}
Here's another example where I want to sum the sales by salesman, but only where sales are greater than 15 in the month, maybe for commission calculations. By convention, I've made any input cells blue, to indicate that my end user can change the sales threshold figure to anything he or she wants.
My base function is a SUM, so Method 2 works well here. Again, you could write this as an advanced SUMPRODUCT.
{=SUM(($B$5:$B$28=B43)*($E$5:$E$28>$C$42)*$E$5:$E$28)}
The last example is a simple table of sales by salesman and month. You could also use advanced SUMPRODUCT to create this summary, but a Pivot Table would not work. Pivot tables won't output data in an as controllable format, whereas Array statements are very easily formatted.
{=SUM(($B$5:$B$28=$B48)*($C$5:$C$28=C$47)*$E$5:$E$28)}
Things to watch out for
- If you edit an Array formula, remember to activate it with CTRL+SHIFT+ENTER
- Arrays have to be the same size
- Don't write an Array when a SUMIF or COUNTIF will do. You'll unnecessarily complicate things
Well, that was a bit of a whirlwind and we've only scratched the surface of Arrays, but hopefully that helps explain what they are, how they act, and how to use them.
Notes
| Last updated | 12/12/07 |
| Application Version | Excel 2003 |
| Author | Michael Kan |
| Pre-requisites | None |
| Related Tips |
Advanced COUNTIF/SUMIF Advanced SUMPRODUCT Pivot Tables |