Find the 2nd highest value in a serie.

[getco]

How do I find the 2nd highest value in a series? If A1 cell & find the value between b1:m2 then I get the next highest value. I just want to know how to do the second highest. pl give the formula. Details as under.


A1=11280 then next highest value in column A3 11600

A1=14720 then next highest value in column A3 14730


column wise series

10300 10610 10930 11260 11600 11950 12310 12680 13070 13470 13880 14300 14730 15180 15640 16110 16600 17100

 

[vijay.vizzu]

Hi Getco


You can use MATCH+INDEX function to get next highest value. Assume that in A1=11280 and your data range is B1:S1, then in A3 the formula should be =INDEX(B1:S1,0,MATCH(A1,$B$1:$S$1,1)+1). I hope it will work for you.


Thanks

Vijay

 

[keymaster]

@Getco...


Welcome to Chandoo.org forums and thanks for your question.


You can use LARGE formula for this.


try =LARGE(b1:m2,2) to get the second highest value.

 

[amrit1186]

Hi Getco


In case the values may repeat in the array, the following formula can be used.


=LARGE(A1:A9,COUNTIF(A1:A9,MAX(A1:A9))+1)


Cheers,

Amritansh

 

[getco]

Thanks, Vijaybhai, This is perfect formula.

 

[shrivallabha]

You can also try following formula:

=MIN(IF(B2:S2>A1,B2:S2))

It needs to be array entered (CTRL + SHIFT + ENTER).

 

[getco]

Thanks, shrivallabha, This is also perfect formula.