how do I find the SECOND highest value in a series?

[t_h_e_train]

How do I find the 2nd highest value in a series? If i do Max(d4:d11) then I get the max. I just want to know how to do the second highest, max minus one, as it were.
Appreciate the help. I imagine it's an easy query. Thanks.D
This message was edited by t_h_e_train on 2002-08-14 16:33

 

[T. Valko]

Yipppeeee !! this is what i exactly wanted.. Thanks a million Mr. Biff !!! I wish i know excel like you do

Also can you explain me how this formula works.. i managed to figure out till IF(CHOOSE({1,2,3,4},A2,A4,B3,B6)>=TODAY(),CHOOSE({1,2,3,4},A2,A4,B3,B6)

But dint understand the IFERROR(1/(1/MIN

Please help me understand so that i can use this formula some time in the future if reqd.

Cheers,
Prash

Try this experiment.

Let's assume cell B6 contains the date 10/31/2012 and the formula returns that date as the result.

In some cell enter this formula:

=1/B6

Format this cell as Number and take it out to 15 decimal places. You will see this number: 0.000024264188484

In Excel, dates are really just numbers formatted to look like dates. These numbers are commonly referred to as the date serial number. The date serial nuimber is the count of days starting from a base date. The default base date is January 1, 1900. So, January 1, 1900 is date serial number 1.

Jan 1 1900 = date serial number 1
Jan 2 1900 = date serial number 2
Jan 3 1900 = date serial number 3
Jan 4 1900 = date serial number 4
Jan 5 1900 = date serial number 5
etc
Jan 1 1975 = date serial number 27395
Jan 1 2000 = date serial number 36526
Oct 31 2012 = date serial number 41213

You can see the date serial number by entering some date in a cell and then changing that cells format to General or Number.

OK, back to our experiment.

=1/B6

So, what we're doing is dividing 1 by the date serial number 41213:

=1/41213 = 0.000024264188484

Now, if we divide 1 by that number:

=1/0.000024264188484 = 41231

We can say that 1/(1/N) = N except when N=0.

So, if B6 contians the date 10/31/2012 (serial number 41213), then:

=1/(1/B6) = 41213 (date 10/31/2012)

However!

When there are no dates that meet the condition then the MIN function will return 0 which we don't want.

So, we get this:

1/0 = #DIV/0!

1/(1/0) = #DIV/0!

Then the IFFERROR function traps that #DIV/0! error and returns blank.

 

[Prashanthgangala]

Thankyou for the explanation that was very clear !!!

 

[T. Valko]

Thankyou for the explanation that was very clear !!!

You're welcome. Thanks for the feedback!