second highest value

[Pivotrend]

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

[Bob Phillips]

=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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Ron Rosenfeld]

On Tue, 21 Feb 2006 07:09:22 -0600, Pivotrend
<[email protected]> 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

[Pivotrend]

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

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Pivotrend" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[Ron Rosenfeld]

On Tue, 21 Feb 2006 09:36:23 -0600, Pivotrend
<[email protected]> 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

[Pivotrend]

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