Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles MIN AND MAX nth largest value with duplicates

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:

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc