Microsoft Office Tutorials and References
In Depth Information
This formula eliminates the need for the IF function and takes advantage of the fact that
TRUE * 1 = 1
FALSE * 1 = 0
TRUE and FALSE in array formulas
When your arrays return Boolean values (TRUE or FALSE), you must coerce these Boolean values into numbers.
Excel's SUM function ignores Booleans, but you can still perform mathematical operations on them. In Excel,
TRUE is equivalent to a value of 1, and FALSE is equivalent to a value of 0. Converting TRUE and FALSE to
these values ensures the SUM function treats them appropriately.
You can use three mathematical operations to convert TRUE and FALSE to numbers without changing their val-
ues, called identity operations.
• Multiply by 1: (x * 1 = x)
• Add zero: (x + 0 = x)
• Double negative: (–– x = x)
Applying any of these operations to a Boolean value will cause Excel to convert it to a number. The following for-
mulas all return the same answer:
There is no “best” way to convert Boolean values to numbers. Pick a method that you like and use that. However,
be aware of all three methods so that you can identify them in other people's spreadsheets.
Eliminating intermediate formulas
One of the main benefits of using an array formula is that you can eliminate intermediate formulas in your
worksheet. This makes your worksheet more compact and eliminates the need to display irrelevant calculations.
Figure 14-17 shows a worksheet that contains pre-test and post-test scores for students. Column D contains for-
mulas that calculate the changes between the pre-test and the post-test scores. Cell D17 contains the following
formula, which calculates the average of the values in column D:
With an array formula, you can eliminate column D. The following array formula calculates the average of the
changes but does not require the formulas in column D: