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.

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:
Solution examples
Given: RetYr = 2057 B48=2055 B49=2056 B50=2057 FirstYrAnnExp = 174,855 B51=2058 M48=IF(B48>=RetYr-1,IF(M47>1,M47*(1+Inflation),FirstYrAnnExp)) Returns FALSE M49=IF(B49>=RetYr-1,IF(M48>1,M48*(1+Inflation),FirstYrAnnExp)) Correctly retuns 0 M50=IF(B50>=RetYr-1,IF(M49>1,M49*(1+Inflation),FirstYrAnnExp)) Correctly returns 174,855 M51=IF(B51>=RetYr-1,IF(M50>1,M50*(1+Inflation),FirstYrAnnExp)) Correctly returns 180,100 How can I avoid “FALSE” when the B column is less than RetYr? A zero or blank would be better.
Solved by V. W. in 20 mins
Hi Can you see any glaring error with the below formula and help me why it wont work? =IF(E8<400,J6,(IF(E8<=1200,J5)),(IF(E8>=1201,J4)))*E8 Thanks Em
Solved by V. C. in 40 mins
I need a formula that looks up if the date (which looks like "8/1/2018 8:05:36 AM") in Column "C" contains year 2018. And then populates "2018" in Column D, if the date does not contain "2018" then enter False in Column D.
Solved by F. H. in 20 mins
I am trying to have a tab change color when the Sum of a row = 0. I am using code on the tab but it appears to be confused because the value of the given cell is not 0 it is "=SUM(G4:G17)"
Solved by S. F. in 41 mins
Could you help me by explaining the IF function?
Solved by G. L. in 24 mins

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