|
second highest digit 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 thank you -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062 View this thread: http://www.excelforum.com/showthread...hreadid=514745 |
second highest digit value
=LARGE(A$1:A$9,COUNTIF(A1:A9,LARGE(A$1:A$9,1))+1)
Regards, Stefi €˛Pivotrend€¯ ezt Ć*rta: 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: http://www.excelforum.com/member.php...fo&userid=4062 View this thread: http://www.excelforum.com/showthread...hreadid=514745 |
second highest digit value
didnt work i have thousands on numbers in one clomun not just two -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062 View this thread: http://www.excelforum.com/showthread...hreadid=514745 |
second highest digit value
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: didnt work i have thousands on numbers in one clomun not just two -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062 View this thread: http://www.excelforum.com/showthread...hreadid=514745 |
second highest digit value
didn't work what do i have to change in the formula to get the second highest value , third , forth , & so on ? -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062 View this thread: http://www.excelforum.com/showthread...hreadid=514745 |
second highest digit value
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 |
second highest digit value
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 -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062 View this thread: http://www.excelforum.com/showthread...hreadid=514745 |
second highest digit value
On Tue, 21 Feb 2006 01:11:25 -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 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 |
second highest digit value
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 "Pivotrend" wrote in message ... 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 -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062 View this thread: http://www.excelforum.com/showthread...hreadid=514745 |
second highest digit value
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Pivotrend" wrote: .. 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) 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 |
second highest digit value
Biff Wrote: =MAX(IF(A$1:A$1000<C1,A$1:A$1000)) worked thanx -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php...fo&userid=4062 View this thread: http://www.excelforum.com/showthread...hreadid=514745 |
| All times are GMT +1. The time now is 01:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com