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

 

[PaddyD]

Hi - welcome to the board.

Check out the large function:

with

{10;15;20;19}

in a1:a4,

=large(a1:a4,2)

returns 19
Paddy

 

[t_h_e_train]

Thanks, "Large" worked.

D
This message was edited by t_h_e_train on 2002-08-14 16:44

 

[PaddyD]

Not sure what you mean here? I could understand if you were adding data to the range (in which case it's a simple matter of making the range dynamic). But delete ,e.g., 19 from the range & the formula will return on the next worksheet calculate - no way to get it quicker than that? What do you mean by 'on the fly'.

Paddy

EDIT: just saw you'd deleted your question...
This message was edited by PaddyD on 2002-08-14 16:46

 

[Prashanthgangala]

Small Function works only if the values are in a array, but i need a formula to pick up the 2 smallest value from the list of values located in different cells.
Eg: I need second smallest value from the values in A1, B3, C4, D7, E1

 

[Prashanthgangala]

Can some one help me pleaseee...

 

[Jonmo1]

Although that sounds like a very disorganized data layout....

Try
=SMALL(CHOOSE({1,2,3,4,5},A1,B3,C4,D7,E1),2)

 

[T. Valko]

Small Function works only if the values are in a array, but i need a formula to pick up the 2 smallest value from the list of values located in different cells.
Eg: I need second smallest value from the values in A1, B3, C4, D7, E1

Like this...

=SMALL((A1,B3,C4,D7,E1),2)

 

[Jonmo1]

Like this...

=SMALL((A1,B3,C4,D7,E1),2)

Nice, didn't know you could do that..

 

[T. Valko]

Nice, didn't know you could do that..

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.