Excel 2010 Bible

Chapter 17: Performing Magic with Array Formulas

381

This formula creates an array that consists of 1s (if the corresponding cell contains an error) and 0s

(if the corresponding cell does not contain an error value).

You can simplify the formula a bit by removing the third argument for the IF function. If this
argument isn’t specified, the IF function returns FALSE if the condition is not satisfied (that is, the
cell does not contain an error value). In this context, Excel treats FALSE as a 0 value. The array
formula shown here performs exactly like the previous formula, but it doesn’t use the third argu-
ment for the IF function:

{=SUM(IF(ISERROR(Data),1))}

Actually, you can simplify the formula even more:

{=SUM(ISERROR(Data)*1)}

This version of the formula relies on the fact that

TRUE * 1 = 1

and

FALSE * 1 = 0

Summing the n largest values in a range ..................................................................

The following array formula returns the sum of the 10 largest values in a range named Data:

{=SUM(LARGE(Data,ROW(INDIRECT(“1:10”))))}

The LARGE function is evaluated 10 times, each time with a different second argument ( 1 , 2 , 3 ,
and so on up to 10 ). The results of these calculations are stored in a new array, and that array is
used as the argument for the SUM function.

To sum a different number of values, replace the 10 in the argument for the INDIRECT function
with another value.

If the number of cells to sum is contained in cell C17, use the following array formula, which uses
the concatenation operator (&) to create the range address for the INDIRECT function:

{=SUM(LARGE(Data,ROW(INDIRECT(“1:”&C17))))}

To sum the n smallest values in a range, use the SMALL function instead of the LARGE function.

Computing an average that excludes zeros ..............................................................

Figure 17.2 shows a simple worksheet that calculates average sales. The formula in cell B13 is

=AVERAGE(B4:B11)