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
This is a discussion on how do I find the SECOND highest value in a series? within the Excel Questions forums, part of the Question Forums category; How do I find the 2nd highest value in a series? If i do Max(d4:d11) then I get the max. ...
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 ]
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
Thanks, "Large" worked.
D
[ This Message was edited by: t_h_e_train on 2002-08-14 16:44 ]
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 ]
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
Can some one help me pleaseee...
Although that sounds like a very disorganized data layout....
Try
=SMALL(CHOOSE({1,2,3,4,5},A1,B3,C4,D7,E1),2)
Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac
The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Ferris Bueller A.K.A. John Hughes, 1986
Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac
The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Ferris Bueller A.K.A. John Hughes, 1986
.
Biff
Microsoft MVP - Excel
Don't be afraid to use volatile functions or array formulas
Tell us what version of Excel you're using
KISS - Keep It Simple Stupid
Like this thread? Share it with others