Microsoft Office Tutorials and References

In Depth Information

**Chapter 15: Performing Magic with Array Formulas**

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 corresponds 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.

The following array formula is similar to the previous one, but it returns the actual cell address of

the maximum value in the
Data
range. It uses the ADDRESS function, which takes two arguments:

a row number and a column number.

{=ADDRESS(MIN(IF(Data=MAX(Data),ROW(Data), “”)),COLUMN(Data))}

The previous formulas work only with a single-column range. The following variation works with

any sized range and returns the address of the smallest value in the range named
Data
:

{=ADDRESS(MIN(IF(Data=MAX(data),ROW(Data), “”)),

MIN(IF(Data=MAX(Data),COLUMN(Data), “”)))}

Finding the row of a value’s
nth occurrence in a range

The following array formula returns the row number within a single-column range named
Data

that contains the
nth occurrence of the value in a cell named
Value
: