second highest digit 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

thank you

 

[Guest]

=LARGE(A$1:A$9,COUNTIF(A1:A9,LARGE(A$1:A$9,1))+1)
Regards,
Stefi


„Pivotrend†ezt írta:

 

[Pivotrend]

didnt work
i have thousands on numbers in one clomun
not just two

 

[Guest]

Sorry, 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

„Pivotrend†ezt írta:

 

[Pivotrend]

didn't work

what do i have to change in the formula to get the second highest valu
, third , forth , & so on

 

[vezerid]

OK, 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("1:"&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

 

[Pivotrend]

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

 

[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

thank you

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

Then use this formula:

=LARGE(UNIQUEVALUES(rng),2)


--ron

 

[Biff]

C1 = formula:

=MAX(A1:A1000)

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

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

Copy down until you get returns of 0.

Biff

 

[Max]

Just another play to try ..
(perhaps a bit easier to understand and implement <g>)

A sample implementation is available at:
http://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="","",IF(COUNTIF($A$1:A1,A1)>1,"",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$1000<>"",$A$1:$A$1000),ROW())),"",LARGE(IF($B$1
:$B$1000<>"",$A$1:$A$1000),ROW()))

Copy C1 down until "blanks" 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

---

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

(orig. post)

 

[Pivotrend]

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

worked

thanx