Excel Annoyances

ARRAY FORMULA ANNOYANCES

DEPLOY ARRAY FORMULAS

The Annoyance:

I inherited an inventory-tracking workbook (shown in Figure 3-14) from an engineer in my custom computer-building company. Somehow, she created what appears to be a formula that calculates the total value of our inventory by multiplying the number of each part on hand by that part’s price, and then adding the individual results. The formula, which is stored in cell D7, is {=SUM(B2:B6*C2:C6)}. Why are there curly braces around the formula, and why do I get a VALUE! error when I try to edit the formula?

Figure 3-15. The formula in cell D7 lets you skip a lot of partial calculations in cells D2:D6.

The Fix:

The formula in question is an array formula, which means it operates on ranges of cells instead of on individual cells. To see how an array formula works, break the formula {=SUM(B2:B6*C2:C6)} into its component parts. The innermost operation, B2:B6*C2:C6, multiplies the range B2:B6 by the range C2:C6, which means cell B2 is multiplied by cell C2, B3 by C3, and so on. Instead of writing the individual results to cells in the worksheet, Excel maintains the results in an array in program memory. The second operation, SUM, tells Excel to add the values in the results in memory and display the result in the cell that contains the formula (D7 in this case).

To turn a formula into an array formula, type it as a normal formula, ...