second highest value
 

cells

A1:2200
A2:2100
A3:2200
A4:2200
A5:2200
A6:2180
A7:2190
A8:2200
A9:2100

i know the formula =LARGE(A1:A9,1)
i get 2200
which is A1 A3 A4 A5 A8
but i want the second one in value

when i type =LARGE(A1:A9,2)
i get 2200
but i want 2190
which is A7 to be the second result

what formula do i have to use


--
Pivotrend
------------------------------------------------------------------------
Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062
View this thread: http://www.excelforum.com/showthread...hreadid=514858

second highest value
 

=MAX(IF(A1:A9<LARGE(A1:A9,1),A1:A9))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Pivotrend" wrote in
message ...

cells

A1:2200
A2:2100
A3:2200
A4:2200
A5:2200
A6:2180
A7:2190
A8:2200
A9:2100

i know the formula =LARGE(A1:A9,1)
i get 2200
which is A1 A3 A4 A5 A8
but i want the second one in value

when i type =LARGE(A1:A9,2)
i get 2200
but i want 2190
which is A7 to be the second result

what formula do i have to use


--
Pivotrend
------------------------------------------------------------------------
Pivotrend's Profile:
http://www.excelforum.com/member.php...fo&userid=4062
View this thread: http://www.excelforum.com/showthread...hreadid=514858

second highest value
 

On Tue, 21 Feb 2006 07:09:22 -0600, Pivotrend
wrote:


cells

A1:2200
A2:2100
A3:2200
A4:2200
A5:2200
A6:2180
A7:2190
A8:2200
A9:2100

i know the formula =LARGE(A1:A9,1)
i get 2200
which is A1 A3 A4 A5 A8
but i want the second one in value

when i type =LARGE(A1:A9,2)
i get 2200
but i want 2190
which is A7 to be the second result

what formula do i have to use

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this formula:

=LARGE(UNIQUEVALUES(rng),2)


--ron

second highest value
 

didn't work
i still need to know the formula & what to change in the formula to get
the second Highest value , third , forth , fifth & so on


--
Pivotrend
------------------------------------------------------------------------
Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062
View this thread: http://www.excelforum.com/showthread...hreadid=514858

second highest value
 

In what way did it not work, it worked fine for me.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Pivotrend" wrote
in message ...

didn't work
i still need to know the formula & what to change in the formula to get
the second Highest value , third , forth , fifth & so on


--
Pivotrend
------------------------------------------------------------------------
Pivotrend's Profile:
http://www.excelforum.com/member.php...fo&userid=4062
View this thread: http://www.excelforum.com/showthread...hreadid=514858

second highest value
 

On Tue, 21 Feb 2006 09:36:23 -0600, Pivotrend
wrote:


didn't work
i still need to know the formula & what to change in the formula to get
the second Highest value , third , forth , fifth & so on

What does "didn't work" mean, exactly?

It is difficult to troubleshoot with such a nebulous description of the
problem.

So far as what to change to get at the third, fourth, etc., look at HELP for
the LARGE command.


--ron

second highest value
 

it keeps showing the second value only
not the third or forth or fifth.........

what do i need to change in the formula to get the second value , third
, forth , fifth & so on


--
Pivotrend
------------------------------------------------------------------------
Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062
View this thread: http://www.excelforum.com/showthread...hreadid=514858