Exceljet

Quick, clean, and to the point

nth largest value

Excel formula: nth largest value
Generic formula 
=LARGE(range,n)
Summary 

To get the 2nd largest value, 3rd largest value, 4th largest value, and so on, from a set of data, you can use the LARGE function. In the example shown, the formula in I5 is:

=LARGE($C5:$G5,I$4)

As the formula is copied across and down the table, it returns the top 3 scores for each student in the list.

Explanation 

The LARGE function is fully automatic — you just need to supply a range and an integer for"nth" to specify the ranked value you want. The official names for these arguments are "array" and "k".

For example we can use LARGE to get the top 3 scores for Hannah like this:

 =LARGE(C5:G5,1) // best score
 =LARGE(C5:G5,2) // 2nd best score
 =LARGE(C5:G5,3) // 3rd best score

In the example shown, the formula in I5 looks like this:

=LARGE($C5:$G5,I$4)

This is a clever use of mixed references that takes advantage of the numbers 1,2, and 3 already in the range I5:K5, so that they can be plugged into the formula for n:

  • The value given for array is the mixed reference $C5:$G5. Notice columns are locked, but rows are not. This allows the rows to update as the formula is copied down, but prevents columns from changing as the formula is copied across.
  • The value given for k (n) is another mixed reference, I$4. Here, the row is locked so that it will not change as the formula is copied down. However, the column is not locked, allowing it to change as the formula is copied across.

Note: use the SMALL function to get the nth smallest value.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.