Part II: Working with Formulas and Functions
If two values in the list have the same value, LARGE and SMALL return the same value for the Nth value and the Nth
+ 1 value. If two competitors had a score of 588, =LARGE($C$3:$C$14,1) and =LARGE($C$3:$C$14,2)
would both return 588.
In cell F3, we use ROW(A1) to determine N. The ROW function returns the row for the cell
passed to it, row 1 in this case. We could simply pass the number 1 to the LARGE func-
tion, but by using ROW(A1), we can copy this formula down to increase the row. The A1
reference is relative, and when the formula is copied to cell F4, it becomes ROW(A2). That
returns 2, and the LARGE function in F4 then returns the second largest value.
See Chapter 9 for more information on absolute and relative cell references.
The LARGE function is appropriate here because higher bowling scores are better. If,
instead, we had a list of race times, then the SMALL function would be used because lower
times are better.
Another way to determine the Nth largest or smallest number is the RANK function. The
RANK function takes three arguments: the number to be ranked, the list of all the numbers,
and the sort order. Figure 16.10 shows another result of a competition, but this time the
lowest time is the winner. It also includes another column to rank each result, and that
formula is shown here:
=RANK(C3,$C$3:$C$14,1)
FIGURE 16.10
The results of a race
