Microsoft Office Tutorials and References
In Depth Information
Using Single-Cell Array Formulas
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 values, 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 formulas all return the same answer:
{=SUM(ISTEXT(A1:D5)*1)}
{=SUM(ISTEXT(A1:D5)+0)}
{=SUM(--ISTEXT(A1:D5))}
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-16 shows a worksheet that contains pre-test and post-test
scores for students. Column D contains formulas 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:
=AVERAGE(D2:D15)
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:
{=AVERAGE(C2:C15-B2:B15)}
 
Search JabSto ::




Custom Search