Page 380 - Excel 2007 Bible

23_044039 ch18.qxp 11/21/06 11:09 AM Page 337
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.
Yet another approach uses the COUNTIF function in a non-array formula:
=IF(COUNTIF(NameList,TheName)>0,”Found”,”Not Found”)
Counting the number of differences in two ranges
The following array formula compares the corresponding values in two ranges (named MyData and
YourData) and returns the number of differences in the two ranges. If the contents of the two ranges are
identical, the formula returns 0.
{=SUM(IF(MyData=YourData,0,1))}
The two ranges must be the same size and of the same dimensions.
NOTE
NOTE {=OR(TheName=NameList)} Performing Magic with Array Formulas 18
This formula works by creating a new array of the same size as the ranges being compared. The IF function
fills this new array with 0s and 1s. (0 if a difference is found, and 1 if the corresponding cells are the same.)
The SUM function then returns the sum of the values in the array.
The following formula, which is simpler, is another way of calculating the same result:
{=SUM(1*(MyData<>YourData))}
This version of the formula relies on the fact that:
TRUE * 1 = 1
and
FALSE * 1 = 0
Returning the location of the maximum value in a range
The following array formula returns the row number of the maximum value in a single-column range
named Data:
{=MIN(IF(Data=MAX(Data),ROW(Data), “”))}
The IF function creates a new array that corresponds to the Data range. If the corresponding cell contains
the maximum value in Data, the array contains the row number; otherwise, it contains an empty string. The
MIN function uses this new array as its second argument, and it returns the smallest value, which corre-
sponds to the row number of the maximum value in Data.
If the Data range contains more than one cell that has the maximum value, the row of the first maximum
cell is returned.

337