Array formulas are an underutilized and powerful tool in Excel. An array formula is a formula that works with an array (or series) of data values rather than a single data value.
Array formulas work in two different ways, the first being a formula that returns a result in two or more cells that are “grouped” with one formula. While this application can be useful, especially for linear algebraic matrix calculations, we will focus on its primary use.
The main use of array formulas is to aggregate an array, typically using SUM, AVERAGE, or COUNT, to return a single value to a single cell. This can be very convenient for combining one particular subset of data without resorting to a pivot table.
For example, in its simple form, the formula =ROW(A1:A10) returns the number 1, which is the row number of the first cell in the range A1:A10. However, if this is entered as an array formula, it will return an array or series of numbers, each of which is the row number of a cell in the range A1:A10. That is, instead of returning the single value 1, it returns the array of numbers {1, 2, 3, 4, 5, 6, 7, 8, 9, 10}. (In standard notation, arrays are written enclosed in curly braces { }.) When using array formulas, you typically use a container function such as SUM, COUNT, or AVERAGE to aggregate the array to a single number result. Expanding on the example above, the formula =SUM(ROW(A1:A10)) entered normally will return a value of 1. This because in its normal mode, ROW(A1:A10) returns a single number, 1, and then SUM just sums that single number. However, if the formula is entered as an array formula, ROW(A1:A10) returns the array of row numbers and then SUM adds up the elements of the array, giving a result of 55 ( = 1 + 2 + 3 + ... + 10).So, how do you create an array formula?
The most important thing to know about array formulas is how they are entered. To enter a formula as an array formula, type the formula in the cell and press the CTRL, SHIFT, and ENTER keys at the same time rather then just ENTER. You must do this the first time you enter the formula and whenever you edit the formula later. If you do this properly, Excel will display the formula enclosed in curly braces { }. You do not type in the braces -- Excel will display them automatically. If you neglect to enter the formula with CTRL-SHIFT-ENTER, the formula may return a #VALUE error or return an incorrect result.
The IF function can be used in an array formula to test the result of multiple cell tests at one time. For example, you may want to compute the average of the values in A1:A5 but exclude numbers that are less than or equal to zero. For this, you would use an array formula with an IF function to test the cell values and an AVERAGE function to aggregate the result. The following formula does exactly that:
=AVERAGE(IF(A1:A5>0,A1:A5,FALSE))
When you press CTRL, SHIFT, and ENTER, the cell formula will look like this:
{=AVERAGE(IF(A1:A5>0,A1:A5,FALSE))}
This works by testing if each cell in A1:A5 is greater than 0 and returning that number if TRUE and if not, returning FALSE. Something like {1, FALSE, 3, 4, FALSE}, if A2 and A5 were less than 0. Average functions ignore FALSEs and would thus average 1, 3, and 4.
These formulas really show their power with logical operations. Consider the following data:
To find all the PCs that Smith sold you would use the following formula by entering what’s inside the squiggly brackets and pressing CTRL, SHIFT, and ENTER:
{=SUM(IF(("PC"=A2:A10)*("Smith"=B2:B10),C2:C10))}
Your result will be 150.
The asterisk (*) is used as a logical AND because it multiplies the two criteria so any FALSE for either of the two criteria results in a FALSE for the product. In the above data, ("PC"=A2:A10) would return {FALSE, FALSE, TRUE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE} and ("Smith"=B2:B10) would return {FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, FALSE, TRUE}. The product of these arrays would return {FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, TRUE}, so the final step of the function would be SUM {0, 0, 0, 0, 0, 60, 0, 0, 90} (=150)
Similar methodology can be used to show that the plus sign (+) can be used as a logical OR statement, for instance:
{=SUM(IF(("PC"=A2:A10)*(("Smith"=B2:B10)+("Jones"=B2:B10)),C2:C10))}
The result being 180 for PCs sold by either Smith or Jones.
The Evaluate Formula button on the FORMULAS tab of the ribbon can help you step through the evaluation process to better understand how it works.
Array formulas are very powerful tools that allow you to do things that are not possible with regular formulas. Although they may seem complicated at first, you'll find that with a little practice they are quite logical. Enjoy!
