nth largest value with duplicates

If you have a data set with duplicates, and you want to get ‘nth largest value,’ use MAX together with IF functions. NOTE: the LARGE function may not work if the nth values exist in the data since it will return duplicates.

Generic Formula

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

where

(rng) is the range of Excel cells and A2 is the largest value in the range.

Example

Assuming that you have a series of values and you want to get the nth largest value, this is how the formula can be applied to get the nth largest value with duplicates: Check out this example:

Figure 1. Example 1 of MAX and IF Function

From the above example, the formula in F9:

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

…….where “rng’ is the data range C6:C12

How the For Formula Works

To get the nth largest value with duplicates, we must start with getting the largest value in the data. That is where you need the MAX function and the cell is F8. Here’s the formula:

=MAX(rng)

=MAX(C6:C12)

= 34

Once you have the largest, then you need to get a formula that will check all the values in the range that you’ve provided against the ‘last large value’. Here is the formula:

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

….. it is important to note that this formula can only be entered with ctrl+shift+enter.

In this formula, all values that are larger than the “last largest” return as FALSE while those that are lower will survive the test. This is how the result looks like:

{32;FALSE;FALSE;31;32;32;30}

Copy the formula down the formula downward and the table will auto fill. That’s how you get the nth largest value with duplicates using MAX together with IF Function.