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 :
 
Search JabSto ::




Custom Search