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