Excel 2010 Bible

Part II: Working with Formulas and Functions

384

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:

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

Note
The two ranges must be the same size and of the same dimensions. n

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: 1 if a difference is found, and 0 if the corresponding

cells are the same. The SUM function then returns the sum of the values in the array.

The following array 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