Excel formula: nth largest value with duplicates

Generic formula

=MAX(IF(rng<A2,rng))

Summary

To get the nth largest value in a data set with duplicates, you can use an array formula based on the MAX and IF functions.

Note: the LARGE function will easily return nth values, but LARGE will return duplicates when they exist in the source data.

In the example shown, the formula in E6 is:

=MAX(IF(rng<E5,rng))

Where "rng" is the named range B5:B11

Explanation

First, we get the largest value using the MAX function in E5:

=MAX(rng)

Once we have the largest value established, we create another formula that simply checks all values in the named range "rng" against the "last largest value":

=MAX(IF(rng<E5,rng))

Note: this is an array formula and must be entered with control + shift + enter.

Any value that is lower than the "last largest" survives the test, and any value that's not ends up FALSE. The resulting array looks like this:

{12;FALSE;FALSE;11;12;12;10}

As the formula is copied down the column, E5 increments at each row, so that MAX and IF return a new array. The MAX function continues to return the largest value in each array — the nth value in the series.

It would be great to have an all in one formula that deals with n directly. If you have one, chime in below.

Author

Dave Bruns

Related formulas

nth largest value

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...

nth largest value with criteria

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 problem in this case is that we don't want LARGE to operate on every value in the range, just values that are...

nth smallest value

The SMALL 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". Note: you can also use the MIN function to get the...

Sum top n values

In its simplest form, LARGE will return the "Nth largest" value in a range. For example, the formula: = LARGE ( B4:B13 , 2 ) will return the 2nd largest value in the range B4:B13 which, in the example above, is the number 9. However, if you supply...

Average top 3 scores

The LARGE function is designed to retrieve the top nth value from a set of numbers. So, for example LARGE(A1:A10,1) will return highest value, LARGE(A1:A10,2) will return the 2nd highest value, and so on: LARGE ( range , 1 ) // 1st largest value...

Related functions

Excel MAX Function

The Excel MAX function returns the largest numeric value in a range of values. The MAX function ignores empty cells, the logical values TRUE and FALSE, and text values.

Excel IF Function

The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...

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.