Microsoft Office Tutorials and References

In Depth Information

The AVERAGE function ignores blank cells, but it does not ignore cells that contain 0.

The following array formula (in cell C14) 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 di-

vided 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 val-

ues 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 — or, 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 logic-

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

Yet another approach uses the COUNTIF function in a non-array formula: