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)}