Page 379 - Excel 2007 Bible

23_044039 ch18.qxp 11/21/06 11:09 AM Page 336
Part II
Working with Formulas and Functions
This formula creates a new array that consists only of the nonzero values in the range. The AVERAGE func-
tion then uses this new array as its argument.
You also can get the same result with a regular (non-array) formula:
=SUM(B5:B12)/COUNTIF(B5:B12,”<>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.
Determining whether a particular value appears in a range
To determine whether a particular value appears in a range of cells, you can choose Edit ➪ Find and do a
search of the worksheet. But you also can make this determination by using an array formula.
Figure 18.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 for-
mula displays the text Found. Otherwise, it displays Not Found.
FIGURE 18.3
Using an array formula to determine whether a range contains a particular value.

The array formula in cell D3 is
{=IF(OR(TheName=NameList),”Found”,”Not Found”)}

336