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

t_h_e_train

New Member
Joined
Aug 13, 2002
Messages
4
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Although that sounds like a very disorganized data layout....

Try
=SMALL(CHOOSE({1,2,3,4,5},A1,B3,C4,D7,E1),2)
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top