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

 

[Prashanthgangala]

There's a handful of functions that accept multiple area references.

Off the top of my head:

AVERAGE
COUNT
COUNTA
FREQUENCY
LARGE
MAX
MEDIAN
MIN
RANK
SMALL
SUM

There's probably a few more.

=SMALL((A1,B3,C4,D7,E1),2) works perfectly.. tnx a ton !!

 

[T. Valko]

=SMALL((A1,B3,C4,D7,E1),2) works perfectly.. tnx a ton !!

You're welcome. Thanks for the feedback!

 

[Prashanthgangala]

Hi,
I've another issue !

I've a set of date in a range (mix of past, present (today) and future). I need a formula to pick up the min date which could be today or in the future but not the past.

Eg:
In the below range of dates, if i use Small and pick up the 1 smallest date the formula will pick up 21/Oct/2012, but I want the formula to pick up 24/Oct as 21/Oct has already passed as of today.

10/24/201210/23/2012
10/22/2012
10/21/2012
10/26/2012


Thanks,
Prash

 

[T. Valko]

Hi,
I've another issue !

I've a set of date in a range (mix of past, present (today) and future). I need a formula to pick up the min date which could be today or in the future but not the past.

Eg:
In the below range of dates, if i use Small and pick up the 1 smallest date the formula will pick up 21/Oct/2012, but I want the formula to pick up 24/Oct as 21/Oct has already passed as of today.

10/24/201210/23/2012
10/22/2012
10/21/2012
10/26/2012


Thanks,
Prash

Try this array formula**:

=MIN(IF(A1:A5>=TODAY(),A1:A5))

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

 

[Prashanthgangala]

Hi Biff,
thank you for your quick reply, the formula works perfectly but needs a small tweek, the problem is the dates are not continuous in a row, its spread across different columns. I tried tweeking the formula as =MIN(IF((A2,B3,A4,B6)>=TODAY(),(A2,B3,A4,B6))) but it dint work. Can you help me please !!

thanks,
Prash

 

[T. Valko]

Hi Biff,
thank you for your quick reply, the formula works perfectly but needs a small tweek, the problem is the dates are not continuous in a row, its spread across different columns. I tried tweeking the formula as =MIN(IF((A2,B3,A4,B6)>=TODAY(),(A2,B3,A4,B6))) but it dint work. Can you help me please !!

thanks,
Prash

The IF function doesn't directly accept multiple area references so it takes a bit more.

Array entered**:

=MIN(IF(CHOOSE({1,2,3,4},A2,A4,B3,B6)>=TODAY(),CHOOSE({1,2,3,4},A2,A4,B3,B6)))

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

 

[T. Valko]

The IF function doesn't directly accept multiple area references so it takes a bit more.

Array entered**:

=MIN(IF(CHOOSE({1,2,3,4},A2,A4,B3,B6)>=TODAY(),CHOOSE({1,2,3,4},A2,A4,B3,B6)))

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

Just to add...

If there are no dates that meet the condition the formula will return 0. If you have the formula cell formatted as Date then it'll display something like 1/0/1900.

If you need to account for that let me know. Also, let me know what version of Excel you're using.

 

[Prashanthgangala]

Just to add...

If there are no dates that meet the condition the formula will return 0. If you have the formula cell formatted as Date then it'll display something like 1/0/1900.

If you need to account for that let me know. Also, let me know what version of Excel you're using.

Hi Biff,
The formula works perfectly, but yes as u pointed out that If there are no dates that meet the condition the formula will return 0, I need to leave the column blank instead of giving a 0 or 1/0/1900. Should i use iserror?

I use excel 2007.

Thanks a lot for your help !!!

Cheers,
Prash

 

[T. Valko]

Hi Biff,
The formula works perfectly, but yes as u pointed out that If there are no dates that meet the condition the formula will return 0, I need to leave the column blank instead of giving a 0 or 1/0/1900. Should i use iserror?

I use excel 2007.

Thanks a lot for your help !!!

Cheers,
Prash

Try this...

=IFERROR(1/(1/MIN(IF(CHOOSE({1,2,3,4},A2,A4,B3,B6)>=TODAY(),CHOOSE({1,2,3,4},A2,A4,B3,B6)))),"")

Still array entered!

 

[Prashanthgangala]

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