Microsoft Office Tutorials and References

In Depth Information

**Chapter 15: Performing Magic with Array Formulas**

The following array formula (in cell B14) returns the average of the range but excludes the cells

containing 0:

{=AVERAGE(IF(B4:B11<>0,B4:B11))}

This formula creates a new array that consists only of the nonzero values in the range. The

AVERAGE function then uses this new array as its argument.

You also can get the same result with a regular (non-array) formula:

=SUM(B4:B11)/COUNTIF(B4:B11,”<>0”)

This formula uses the COUNTIF function to count the number of nonzero values in the range. This

value is divided into the sum of the values. This formula does not work if the range contains any

blank cells.

The only reason to use an array formula to calculate an average that excludes zero

values is for compatibility with versions prior to Excel 2007. A simple approach is to use

the AVERAGEIF function in a non-array formula:

=AVERAGEIF(B4:B11,”<>0”,B4:B11)

Determining whether a particular value appears in a range

To determine whether a particular value appears in a range of cells, you can press Ctrl+F and do

a search of the worksheet. But you can also make this determination by using an array formula.

Figure 15-3 shows a worksheet with a list of names in A5:E24 (named
NameList
). An array

formula in cell D3 checks the name entered into cell C3 (named
TheName
). If the name exists in the

list of names, the formula then displays the text
Found
. Otherwise, it displays
Not Found
.

The array formula in cell D3 is

{=IF(OR(TheName=NameList),”Found”,”Not Found”)}

This formula compares
TheName
to each cell in the
NameList
range. It builds a new array that

consists of logical TRUE or FALSE values. The OR function returns TRUE if any one of the values

in the new array is TRUE. The IF function uses this result to determine which message to display.

A simpler form of this formula follows. This formula displays TRUE if the name is found and

returns FALSE otherwise.

{=OR(TheName=NameList)}