Chapter 17: Performing Magic with Array Formulas
385
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.I use the MIN function to handle ties. If the Data range contains more than one cell that has the
maximum value, the row of the first occurrence of the maximum cell is returned. If you change
MIN to MAX, then the formula returns the last occurrence of the maximum cell.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:{=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 nth smallest row number.
The formula returns #NUM! if the Value is not found or if n exceeds the number of the values 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)}This formula works with two arrays, both of which contain the length of each item in the Data
range. The MAX function determines the largest value, which corresponds to the longest text item.
The MATCH function calculates the offset of the cell that contains the maximum length. The INDEX
function returns the contents of the cell containing the most characters. This function works only if
the Data range consists of a single column.