Microsoft Office Tutorials and References

In Depth Information

{=SUM(ISTEXT(A1:D5)*1)}

This formula eliminates the need for the IF function and takes advantage of the fact that

TRUE * 1 = 1

and

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:

{=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-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:

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