Finding the second highest value in the list

[LAVA2]

There is a coloumn which has 1000 sets of datas starting from a2:a1001 and at a3 i have managed to find the highest value in that range which is 80 by using the formula =MAX(IF(a2:a1001>0,a2:a1001,FALSE)).

Now at a4 i want to find the second highest value in that range which is 75 and at a5 the third highest value in that range , which is 60 and so on by using a formula.

Can anyone help me to find a proper answer?


note: in the range there are repeated values.


Thanks in advance

[TMS]

Use

Please Login or Register  to view this content.

Regards, TMS

[tuph]

Instead of your formula use this in A3 =LARGE(H6:H140,1) to return the highest number in the range. By changing it to =LARGE(H6:H140,2) in A4 it will return the second highest number and so on.

[Aladin Akyurek]

Do you really need to do the >0 test? Is it not just...

C2, just enter:

=LARGE($A$2:$A$1001,ROWS($C$2:C2))

and copied down?

[Crooza]

I'm guessing you mean b3 and b4 rather than a3 and a4 as this is part of your data set.

Why not try =large(a2:a1001,2) for 2nd largest and =large(a2:a1001,3) for third largest and so on

[:) Sixthsense :)]

Also you can use array like this...


=SUM(LARGE(A:A,{1,2,3,4,5}))

[LAVA2]

Hi,

Thanks for your support.

I have some questions just to simplify the task:
I did typed =sum(large(a1:a20,{1})) in cell b1 to get the first highest it did worked well to get the first highest. Then i did typed =sum(large(a1:a20{2})) in b3 and it also returned the same value as in b1 which is 20. In the range there are only three values and all of them are 20. My hope was to get 0 in b3 not 20 as the second highest.

Also i'am looking for something like this as a bonus adjustment:
When i paste the same formula in any cells, i would like that constant number within braces to be also increased by 1.
Lets say first paste in any cell to become =sum(large(a1:a20,{2})), second paste =sum(large(a1:a20,{3})) etc.

If this could happen then it would be of more utility?

[Crooza]

You could type the numbers 1 to 10 say in column B and use =large($a$1:$a$20,B1) and put this in C1 and copy down to C10

Note too I ditched the SUM. You don't need it

[Aladin Akyurek]

@LAVA2

=LARGE($A$1:$A$20,ROWS($B$1:B1))

copied down to B2, and so on.

[LAVA2]

=large($a$1:$a$20,rows($b$1:b1)) also displayed 20 as first second and third consecutively.

[LAVA2]

"Typed the numbers 1 to 10 say in column B and used =large($a$1:$a$20,B1) in C1 and copy down to C10."

Already tried that without success, as again the same problem of repetition.

[Crooza]

If you've got data that is say 100,20,20,20,1 then you'll get 20 as the 2nd the 3rd and the 4th highest numbers.

[Aladin Akyurek]

=large($a$1:$a$20,rows($b$1:b1)) also displayed 20 as first second and third consecutively.

If you have:

A1 = 20
A2 = 15
A3 = 20

and all other values are smaller than the ones above, you' should get:

B1 = 20
B2 = 20
B3 = 15

with the suggested formula.

[LAVA2]

@Crooza your'e right in the context within a certain range, but in reality for practical reasons can same number have same level of increasing magnitude!!

[Crooza]

Yes because its simply an array listed from highest to lowest and the large function counts the nth number in teh array. I assume you don't want multiple occurances so an array of 100,20,20,20,1 gets chnaged to 100,20,1 so highest is 100, 2nd is 20 and third is 1. If so I'll have to think about how to do that. Brighter minds than mine might work it out before I get back to you though. I'll give it some thought

Merry Christmas

[shg]

Is this what you mean?

Row\Col	A	B	C
1	10
2	20	10	B2: =SMALL($A$1:$A$6, COUNTIF($A$1:$A$6, "<=" & B1) + 1)
3	20	20
4	10	30
5	30	40
6	40

[Crooza]

Thanks SHG. Would have taken me a while to get there.

I think the OP wanted the highest to lowest so I took your formula an modified to

Please Login or Register  to view this content.

[LAVA2]

@shg thanks for your solution, actually just would like to ask if it could be bit more flexible?

A) Below is the model that shows how my data should be:


O2= 20 P2= 20 T2= 0 OR Blank X2= 0 or Blank
O3= 20
O4= 20
O5
O6
O7
O8
O9
O10
O11
O12
O13
O14
O15
O16
O17
O18
O19
O20

B) After entering a formula at P2, Is it possible to paste the same formula at T2 and X3 without any modifications and simply acquire Second and Third largest?

[Tony Valko]

You would need separate formulas.

Assuming no negative numbers.

P2: =MAX(O2:O20)

T2: array entered**

=IFERROR(1/(1/MAX(IF(O2:O20<P2,O2:O20))),"")

X3: array entered**

=IFERROR(1/(1/MAX(IF(O2:O20<T2,O2:O20))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[LAVA2]

@Tony Valko what an Ace, JUST MADE MY DAY
And that brilliant formula, I must say you Know Your Onions
Keep it up & MERRY CHRISTMAS

[Tony Valko]

You're welcome. Thanks for the feedback!