Microsoft Office Tutorials and References
In Depth Information
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 largest 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
n th occurrence of the value in a cell named Value :
{=SMALL(IF(Data=Value,ROW(Data), “”),n)}
The IF function creates a new array that consists of the row number of values from the Data range that are equal
to Value . Values from the Data range that aren't equal to Value are replaced with an empty string. The SMALL
function works on this new array and returns the n th smallest row number.
The formula returns #NUM! if the value is not found or if n exceeds the number of occurrences of the value in
the range.
Returning the longest text in a range
The following array formula displays the text string in a range (named Data ) that has the most characters. If
multiple cells contain the longest text string, the first cell is returned.
{=INDEX(Data,MATCH(MAX(LEN(Data)),LEN(Data),FALSE),1)}
Search JabSto ::




Custom Search