Finding SECOND Largest Number (1 Viewer)

[123James]

Hi again

I have used =MAX to find the largest number in a range.

BUT... is there a similar way of finding the second or third or fourth (etc) largest number in a range?

Thanks

 

[Brianwarnock]

I think that you are looking at developing code based on sorting and selecting the required offset, ie if the range was in a1:a50, then having sorted ascending the 48th highest would be in a48.

Brian

 

[Rickster57]

second largest

Found the following for you on the Internet:

For example, if you have a spreadsheet with the following values:

A1: 11
A2: 92
A3: 45
A4: 28
A5: 37


To return the 2nd largest value of the series, which would be 45:

=large(a1:a5,2)

To return the 2nd smallest value of the series, which would be 28:

=small(a1:a5,2)

 

[123James]

Thanks for the suggestion Brian but I dont think that would work for what i need. I need to find the second highest number AFTER the first highest. So if there was mutiples of the biggest number your system would not work would it?

Also i dont know code!!

J Man

 

[123James]

Coool

Wow, nice one rickster!!

Awsome stuff, how did you find that?? never seen it or even heard of it before.

Its worked a treat though. Thanks dude!!

 

[Brianwarnock]

I'm more than impressed Rickster.

Brian

 

[Brianwarnock]

Sorry to be a party pooper but having just tried it =large(a1:a10,2) will return the largest number if there are multiples of it, I guess it does exactly what I was suggesting, if in a more sophisticated way.

Brian

 

[pdx_man]

Try Ye Old Array formula:
Column A
4
5
5
8
8
5

5
=MAX(IF(A1:A6 <> MAX(A1:A6),A1:A6,""))

After pasting this in, be sure to do the Ctrl-Shift-Enter to get the Array Formula. It should then look like:
{=MAX(IF(A1:A6 <> MAX(A1:A6),A1:A6,""))}
Otherwise you will get the #VALUE

 

[Brianwarnock]

Life gets really interesting if you want the third highest value
=MAX(IF(IF(A$1:A$8 <> MAX(A$1:A$8),A$1:A$8,"")<>MAX(IF(A$1:A$8 <> MAX(A$1:A$8),A$1:A$8,"")),(IF(A$1:A$8 <> MAX(A$1:A$8),A$1:A$8,"")),""))

and more?

Brian

I used cells a1:a8 and guess who didn't get it right first time?

 

[shades]

But why bother?

Put the numbers 1-10 in one column (say K1:K10), then in L1 put this formula

=LARGE($A$1:$A$100,K1)

Copy down, and now you have the top ten numbers.

For added benefit, use the same setup and in cell M1 put this:

=SMALL($A$1:$A$100,K1)

Copy down, now you have the 10 smallest numbers. And just extend the formulas in K:M to get more.

Or am I missing something?
________
SILVERSURFER VAPORIZER

 

[pdx_man]

What do you get if you try my values?
4
5
5
8
8
5

The original poster would like 5 returned.

 

[Brianwarnock]

But why bother?

Or am I missing something?

Look at Post#7

Brian

 

[shades]

Yep, in my old age, I hadn't read properly, so I missed something. Sorry to intrude.
________
4MATIC

 

[Brianwarnock]

The fact that James hasn't been back suggests that his question was rhetorical rather than having a practical application. I still say write code, sort, newworksheet?, then remove duplicates, and then ... well you can see where I'm going , just don't ask me to code it.

Brian

 

[123James]

There is a practical application!!

I am using the Array formula PDX Man posted and its working fine.

Thanks for all the help