Maxium Number

[ged18]

Hello, hope the forum can help. I need a max number, 2nd and 3rd highest from a list say A1:A10, I know max(A1:A10,0) and large(A1:A10,2). but if the list has the same highest number
say 8 in more then one cell, A1=8,A6=8,A9=8. I get a result of highest 8, 2nd highest 8, and 3rd highest 8. It works if all the cells have different amounts in each, but not if some have the same. Thanks for you help.

[AliGW]

Are you still using Excel 2007?

For Excel 365:

=LARGE(UNIQUE(A1:A10),1)
=LARGE(UNIQUE(A1:A10),2)
etc.

[ged18]

Thanks for your reply, no I'm using Excel 2019 and the "uniue" is giving me an #name error.

[AliGW]

The solution for offered was for MS365 - sadly you don’t have that.

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

Thank you for helping us to help you.

[AliGW]

Thanks for the Office version - don't forget your broad location (does not need to be more specific than your continent).

[Glenn Kennedy]

One way:

=IFERROR(AGGREGATE(14,6,Sheet1!$A$2:$A$14/(FREQUENCY(Sheet1!$A$2:$A$14,Sheet1!$A$2:$A$14)>0),ROWS(C$2:C2)),"")

[ged18]

Thanks Glenn, brilliant that's exactly what I wanted.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.