software

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

thank you--
Pivotrend
------------------------------------------------------------------------
Pivotrend's Profile: www.excelforum.com/member.php...foamp;userid=4062
View this thread: www.excelforum.com/showthread...hreadid=514745=LARGE(A$1:A$9,COUNTIF(A1:A9,LARGE(A$1:A$9,1)) 1)
Regards,
Stefi?ivotrend??ezt ?rta:

gt;
gt; cells
gt;
gt; A1:2200
gt; A2:2100
gt; A3:2200
gt; A4:2200
gt; A5:2200
gt; A6:2180
gt; A7:2190
gt; A8:2200
gt; A9:2100
gt;
gt; i know the formula =LARGE(A1:A9,1)
gt; i get 2200
gt; which is A1 A3 A4 A5 A8
gt; but i want the second one in value
gt;
gt; when i type =LARGE(A1:A9,2)
gt; i get 2200
gt; but i want 2190
gt; which is A7 to be the second result
gt;
gt; what formula do i have to use
gt;
gt; thank you
gt;
gt;
gt; --
gt; Pivotrend
gt; ------------------------------------------------------------------------
gt; Pivotrend's Profile: www.excelforum.com/member.php...foamp;userid=4062
gt; View this thread: www.excelforum.com/showthread...hreadid=514745
gt;
gt;


didnt work
i have thousands on numbers in one clomun
not just two--
Pivotrend
------------------------------------------------------------------------
Pivotrend's Profile: www.excelforum.com/member.php...foamp;userid=4062
View this thread: www.excelforum.com/showthread...hreadid=514745Sorry, I forgot $ signs from COUNTIF, and of course you have to replace $9 to
the upper limit of your real range, e.g. $5672

=LARGE(A$1:A$9,COUNTIF(A$1:A$9,LARGE(A$1:A$9,1)) 1 )

If you have more problems, please clarify them!

Regards,
Stefi

?ivotrend??ezt ?rta:

gt;
gt; didnt work
gt; i have thousands on numbers in one clomun
gt; not just two
gt;
gt;
gt; --
gt; Pivotrend
gt; ------------------------------------------------------------------------
gt; Pivotrend's Profile: www.excelforum.com/member.php...foamp;userid=4062
gt; View this thread: www.excelforum.com/showthread...hreadid=514745
gt;
gt;


didn't work

what do i have to change in the formula to get the second highest value
, third , forth , amp; so on ?--
Pivotrend
------------------------------------------------------------------------
Pivotrend's Profile: www.excelforum.com/member.php...foamp;userid=4062
View this thread: www.excelforum.com/showthread...hreadid=514745OK, try this: Assuming the rank is in cell C1:

=LARGE($A$1:$A$20,SUM(COUNTIF($A$1:$A$20,LARGE($A$ 1:$A$20,ROW(INDIRECT(quot;1:quot;amp;C1))))) 1)

This will give you the C1_th number in your data set. It is an array
formula, i.e. to be entered with Shift Ctrl Enter.

Does this help?

Kostis Vezerides
didn't work
i still need to know what to change in the formula to get the second
Highest value , third , forth , fifth amp; so on--
Pivotrend
------------------------------------------------------------------------
Pivotrend's Profile: www.excelforum.com/member.php...foamp;userid=4062
View this thread: www.excelforum.com/showthread...hreadid=514745On Tue, 21 Feb 2006 01:11:25 -0600, Pivotrend
gt; wrote:

gt;
gt;cells
gt;
gt;A1:2200
gt;A2:2100
gt;A3:2200
gt;A4:2200
gt;A5:2200
gt;A6:2180
gt;A7:2190
gt;A8:2200
gt;A9:2100
gt;
gt;i know the formula =LARGE(A1:A9,1)
gt;i get 2200
gt;which is A1 A3 A4 A5 A8
gt;but i want the second one in value
gt;
gt;when i type =LARGE(A1:A9,2)
gt;i get 2200
gt;but i want 2190
gt;which is A7 to be the second result
gt;
gt;what formula do i have to use
gt;
gt;thank you

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

Then use this formula:

=LARGE(UNIQUEVALUES(rng),2)--ron

C1 = formula:

=MAX(A1:A1000)

C2 = formula entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(A$1:A$1000lt;C1,A$1:A$1000))

Copy down until you get returns of 0.

Biff

quot;Pivotrendquot; gt; wrote
in message ...
gt;
gt; didn't work
gt; i still need to know what to change in the formula to get the second
gt; Highest value , third , forth , fifth amp; so on
gt;
gt;
gt; --
gt; Pivotrend
gt; ------------------------------------------------------------------------
gt; Pivotrend's Profile:
gt; www.excelforum.com/member.php...foamp;userid=4062
gt; View this thread: www.excelforum.com/showthread...hreadid=514745
gt;
Just another play to try ..
(perhaps a bit easier to understand and implement lt;ggt;)

A sample implementation is available at:
www.savefile.com/files/3893239
Auto Extract Unique Nos in Descending Order.xls

Assuming the source numbers are in A1:A1000

Put in B1 (normal ENTER):
=IF(A1=quot;quot;,quot;quot;,IF(COUNTIF($A$1:A1,A1)gt;1,quot;quot;,ROW()))
Copy B1 down until the last row of data in col A
(Col B flags the unique items in col A)

Put in the formula bar for C1, then array-enter the formula
i.e. press CTRL SHIFT ENTER (instead of just pressing ENTER):
=IF(ISERROR(LARGE(IF($B$1:$B$1000lt;gt;quot;quot;,$A$1:$A$1000 ),ROW())),quot;quot;,LARGE(IF($B$1
:$B$1000lt;gt;quot;quot;,$A$1:$A$1000),ROW()))

Copy C1 down until quot;blanksquot; appear,
signalling exhaustion of data from col A

Col C will return the required results,
i.e. the unique numbers from col A,
all sorted nicely in descending order and bunched at the top

Just adapt the range in the array formula in C1 to suit ..
Note that we can't use entire column references, eg: A:A, B:B
and we have to *array-enter* after we edit the formula
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Pivotrendquot; wrote:
gt; .. didn't work
gt; i still need to know what to change in the formula to get the second
gt; Highest value , third , forth , fifth amp; so on

(orig. post)
gt; A1:2200
gt; A2:2100
gt; A3:2200
gt; A4:2200
gt; A5:2200
gt; A6:2180
gt; A7:2190
gt; A8:2200
gt; A9:2100
gt;
gt; i know the formula =LARGE(A1:A9,1)
gt; i get 2200
gt; which is A1 A3 A4 A5 A8
gt; but i want the second one in value
gt;
gt; when i type =LARGE(A1:A9,2)
gt; i get 2200
gt; but i want 2190
gt; which is A7 to be the second result