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:
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:
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:
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
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
Yet another approach uses the COUNTIF function in a non-array formula: