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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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,248
Messages
6,171,027
Members
452,374
Latest member
keccles

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