LARGE function with an IFNUMBER

**EXCEL PFMT** · 10-08-2012, 10:53 AM

Hi
I have a column of data with numbers and text. I need a formula to find the largest, then the next largest, etc and ignore the text. Here is the formula I am trying and it is not working for me (for the third largest number). Any help or suggestions to get what I need?

{=LARGE(IF(ISNUMBER(G2:G409),(G2:G409,3))}

**jeffreybrown** · 10-08-2012, 10:56 AM

All you need is...

=LARGE($G$2:$G$409,3)

This ignores text.

**icestationzbra** · 10-08-2012, 10:59 AM

you do not need ISNUMBER, LARGER should fetch numbers (unless there are errors in your data range).

also in your formula, the syntax needs to be corrected:

{=LARGE(IF(ISNUMBER($G$2:$G$409),($G$2:$G$409)),3)}

**EXCEL PFMT** · 10-08-2012, 11:12 AM

The syntax correction helped. At least I am getting data now. However, there are many numbers that are the same. How do I get the formula to only give me the same number once? For instance, if there are six occurences of the number 7; one occurence of the number 8 and two occurences of the number 9....I would want the largest to be number 9; then the second largest would be number 8 and the third largest to be number 7. Not number 9; 9; 8; 7; 7; 7; etc.

**jeffreybrown** · 10-08-2012, 11:16 AM

After a search, came up with this which on a quick glance might help you out...

**martindwilson** · 10-08-2012, 11:33 AM

in h2 put
=IF(COUNTIF($G$2:G2,G2)=1,RANK(G2,$G$2:$G$409,1),"") drag down to end
then use in another column filed down to get large 1.2.3.4.........
=INDEX($G$2:$G$409,MATCH(LARGE($H$2:$H$409,ROW(A1)),$H$2:$H$409,0))

**daddylonglegs** · 10-08-2012, 11:42 AM

I think there's a similar version in the link Jeff posted but it's quite well hidden.

Say you want the largest value in J2 you can use this formula in that cell

=MAX(G2:G409)

Then in J3 get the next largest (without repeats) with this formula

=LARGE(G$2:G$409,COUNTIF(G$2:G$409,">="&J2)+1)

and copy that down as far as required

**EXCEL PFMT** · 10-08-2012, 11:51 AM

worked!

**jeffreybrown** · 10-08-2012, 11:53 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save