| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Help! How do you get excel to find the x(changes daily, marked in a cell from another formula) highest numbers in a group of numbers and sum them up? | Excel Worksheet Functions | |||
| Highest Value / Circular Reference Help | Excel Discussion (Misc queries) | |||
| Highest Value / Circular Reference Help | Excel Worksheet Functions | |||
| Return a digit in a string of numbers | Excel Discussion (Misc queries) | |||
| Tell users how to sort 5 digit and 9 digit zipcodes correctly aft. | New Users to Excel | |||
Hybrid Mode
