Find the 2nd highest value in a serie. « Chandoo.org - Learn Excel & Charting Online

Chandoo.org - Learn Excel & Charting Online - Forums » Ask an Excel Question

Find the 2nd highest value in a serie.

(7 posts) (5 voices)

Started 4 months ago by getco
Latest reply from getco

Tags:

getco

Member
Joined: Dec '12
Posts: 4

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

Posted 4 months ago #
vijay.vizzu

Member
Joined: Nov '11
Posts: 227

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

Posted 4 months ago #
Chandoo

Key Master
Joined: Jul '09
Posts: 585

@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.

Posted 4 months ago #
amrit1186

Member
Joined: Dec '12
Posts: 50

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

Posted 4 months ago #
getco

Member
Joined: Dec '12
Posts: 4

Thanks, Vijaybhai, This is perfect formula.

Posted 4 months ago #
shrivallabha

Member
Joined: Apr '12
Posts: 535

You can also try following formula:
=MIN(IF(B2:S2>A1,B2:S2))
It needs to be array entered (CTRL + SHIFT + ENTER).

Posted 4 months ago #
getco

Member
Joined: Dec '12
Posts: 4

Thanks, shrivallabha, This is also perfect formula.

Posted 4 months ago #