Using Excel's Array Formulas Arrays of Light Helen Bradley
Wed 2/6/02 -- Some of the best-kept secrets about Microsoft Excel are its array formulas. These are simple formulas to create, yet they offer the ability to perform calculations in one step that would take many steps if done another way. If you've used Excel for a while, you've probably already encountered situations in which an array formula could come in handy.
Multiply and add. One of the simplest array formulas is one that multiplies a set of numbers in one column by the numbers in the column to the right, and then adds the results. Say your numbers are in the range B1:C10. This formula performs the multiplication and addition in one cell:
=SUM(B1:B10*C1:C10)
To enter it, like all array formulas, you type it, then press Control-Shift-Enter instead of just Enter. If you don't, you'll get a #VALUE! error -- which is easily overcome by pressing F2 as if to edit the cell's contents, then Control-Shift-Enter to reenter the formula correctly. An array formula displays inside a set of curly braces.
Big deal, the Excel wizards in the back row are saying; this array formula gives the same result as using SUMPRODUCT. True, but array formulas let you create calculations to perform complex tasks, which means you don't have to rely on Excel to provide the functions for you. For example ...
Conditional summing. Our friends in the back row are familiar with SUMIF, which allows you to sum a range of values if they meet a condition. But that function can test for only one condition. And while the Conditional Sum Wizard add-in can test for multiple conditions, it can only do so using AND -- it can't perform tests which require an OR operator.
Array formulas don't have these limitations. Using the spreadsheet fragment shown below, here are two array formulas which show how you can sum on the basis of a test for multiple conditions using both AND and OR.
Summing using AND. If you want to calculate the value of hardware sold to Brown, you'd use an AND operator -- the value in column A must be Brown, and the value in column B must be Hardware. This array formula provides the answer (720):
=SUM((A2:A8="Brown")*(B2:B8="Hardware")*(C2:C8))
Summing using OR. What if you want to calculate the value of goods sold to Smith and Jones? You'll use an OR operator -- the value in column A must be either Smith or Jones. This array formula performs the calculation to give the answer (900):
=SUM(((A2:A8="Smith")+(A2:A8="Jones"))*(C2:C8))
How the tests work. Both of these formulas rely on the conditions in brackets returning an answer of true or false (1 or 0) depending on the value of the cell in column A. You can test this by typing =(A2="Smith") into a vacant cell; you'll see the result (TRUE or FALSE) depending on the contents of cell A2.
In each case, the results of the two tests are multiplied or added to give an appropriate multiplier for the remainder of the formula. This provides a result which is the equivalent of using an AND or OR operator.
In the formula =SUM((A2:A8="Brown")*(B2:B8="Hardware")*(C2:C8)), if either or both tests are false, you'll get a multiplier of 0, which effectively removes that line from the calculation. Only if both tests are true will you get a multiplier of 1, ensuring that the line is included in the sum.
Other functions. Array formulas aren't limited to adding values together. You can use other functions in your calculations, including AVERAGE, MAX, MIN, and so on. For example, this array formula returns the average of all hardware purchases made by Brown:
Here we've used an IF test written so it performs as an AND operator, to extract only those figures in the range C2:C8 for which Brown and Hardware appear in columns A and B, respectively. These figures are then passed to the AVERAGE function, which returns the required answer.
Similarly, you can calculate the average of Smith's and Jones's purchases using an IF test that performs as an OR operator, extracting values in column C when the value in column A is either Smith or Jones:
