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)

 

[Ron Rosenfeld]

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 ge
the second Highest value , third , forth , fifth & so o

 

[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)

 

[Ron Rosenfeld]

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