Chapter 16: Using Formulas for Statistical Analysis
16
in the list. INDEX uses that 7 to return the seventh row of the lists of months, or JUL. The
same structure is used for MIN to return JAN, the month with the smallest value.
Both MAX and MIN ignore any text in the range, but if there are errors in the range, they
will return an error. If all the errors are text, MAX and MIN return zero.
Getting the Nth largest or smallest value
The MIN and MAX functions are great for finding the largest and smallest values. But some-
times you need to find the second-largest or the fifth-smallest value. Figure 16.9 shows the
results of a bowling tournament. The bowlers are sorted alphabetically by name, and that
makes it difficult to see who the winners are. We want to identify the first- through third-
place bowlers and their scores. This formula returns the third-largest value from the list of
scores:
=LARGE($C$3:$C$14,ROW(A3))FIGURE 16.9
The results of a bowling tournament
The formula to find the bowler’s name uses INDEX and MATCH, similar to Figure 16.8.
=INDEX($B$3:$B$14,MATCH(F5,$C$3:$C$14,FALSE),1)The LARGE and SMALL functions are used to find the Nth largest and smallest values in a
list. Like MAX, we pass a range of values into LARGE. But LARGE has another argument for
the N in the Nth largest value.