Go Back

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I have a problem regarding to nth largest value
Solved by S. Q. in 25 mins
I need a formula to work with the large formula. I am finding the nth largest numbers and then using Vlookup to find a number in a cell next to this. I don't want it to just return duplicate numbers at the same nth degree if multiple same numbers
Solved by G. A. in 11 mins
I want the nth smallest value in column dynamically
Solved by E. U. in 20 mins

Leave a Comment

avatar