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

Thanks:  0
Likes:  0

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

1.
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 ]

2. Hi - welcome to the board.

Check out the large function:

with

{10;15;20;19}

in a1:a4,

=large(a1:a4,2)

returns 19

3. Thanks, "Large" worked.

D

[ This Message was edited by: t_h_e_train on 2002-08-14 16:44 ]

4. 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'.

EDIT: just saw you'd deleted your question...

[ This Message was edited by: PaddyD on 2002-08-14 16:46 ]

5. ## Re: how do I find the SECOND highest value in a series?

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

6. ## Re: how do I find the SECOND highest value in a series?

Can some one help me pleaseee...

7. ## Re: how do I find the SECOND highest value in a series?

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

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

8. ## Re: how do I find the SECOND highest value in a series?

Originally Posted by 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
Like this...

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

9. ## Re: how do I find the SECOND highest value in a series?

Originally Posted by T. Valko
Like this...

=SMALL((A1,B3,C4,D7,E1),2)
Nice, didn't know you could do that..

10. ## Re: how do I find the SECOND highest value in a series?

Originally Posted by jonmo1
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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•