Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find out the 3 highest occurrence of number from a list?
Does anyone have any suggestions on how to find out the first, second, and
third highest occurrence of number from a list? There is a sorted list of numbers in ascending order under column A, 3,3,3,3,3,4,4,5,5,5,6,6,6,6,6,7,7,9,9,9,10,10,10,1 0,10,11,11,11,11,11, 12,12,12,12,12,12 Since there is 6 occurrence for 12, and there are 5 occurrence for 3,6,10 Then it should return the 12 in cell B1, and 3 in cell C1, and 6 in cell D1, and ignore 10, because I would like to select the 3 numbers only from a list. Does anyone have any suggesitons? Thank in advance for any suggestions Eric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find out the 3 highest occurrence of number from a list?
Use the LARGE function after reducing the list to unique terms with the
advanced filter. "Eric" wrote: Does anyone have any suggestions on how to find out the first, second, and third highest occurrence of number from a list? There is a sorted list of numbers in ascending order under column A, 3,3,3,3,3,4,4,5,5,5,6,6,6,6,6,7,7,9,9,9,10,10,10,1 0,10,11,11,11,11,11, 12,12,12,12,12,12 Since there is 6 occurrence for 12, and there are 5 occurrence for 3,6,10 Then it should return the 12 in cell B1, and 3 in cell C1, and 6 in cell D1, and ignore 10, because I would like to select the 3 numbers only from a list. Does anyone have any suggesitons? Thank in advance for any suggestions Eric |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find out the 3 highest occurrence of number from a list?
=MAX(A1:A20)
=LARGE(IF(MATCH($A$1:$A$20,$A$1:$A$20,0)=ROW($A$1: $A$20)-CELL("Row",$A$1:$A$20)+1,$A$1:$A$20),2) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. and =LARGE(IF(MATCH($A$1:$A$20,$A$1:$A$20,0)=ROW($A$1: $A$20)-CELL("Row",$A$1:$A$20)+1,$A$1:$A$20),3) also an array formula -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Eric" wrote in message ... Does anyone have any suggestions on how to find out the first, second, and third highest occurrence of number from a list? There is a sorted list of numbers in ascending order under column A, 3,3,3,3,3,4,4,5,5,5,6,6,6,6,6,7,7,9,9,9,10,10,10,1 0,10,11,11,11,11,11, 12,12,12,12,12,12 Since there is 6 occurrence for 12, and there are 5 occurrence for 3,6,10 Then it should return the 12 in cell B1, and 3 in cell C1, and 6 in cell D1, and ignore 10, because I would like to select the 3 numbers only from a list. Does anyone have any suggesitons? Thank in advance for any suggestions Eric |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find out the 3 highest occurrence of number from a list?
With your posted data list in A1:A36
Try this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of Enter): C1: =INDEX($A$1:$A$40,MATCH(LARGE(IF(($A$1:$A$40<""), IF(MATCH($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40), COUNTIF($A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),ROW()),IF(($A$1:$A$40<""),IF(MATCH($A$1:$A $40,$A$1:$A$40,0)=ROW($A$1:$A$40),COUNTIF($A$1:$A$ 40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),0)) Note_1: There are NO spaces in that formula Note_2: I had that formula references A1:A40 so that it includes blank cells to ensure that they don't trip up its calculation Note_3: If that formula returns the values you're looking for....we can see about making it more elegant. Does that help? *********** Regards, Ron XL2003, WinXP "Eric" wrote: Does anyone have any suggestions on how to find out the first, second, and third highest occurrence of number from a list? There is a sorted list of numbers in ascending order under column A, 3,3,3,3,3,4,4,5,5,5,6,6,6,6,6,7,7,9,9,9,10,10,10,1 0,10,11,11,11,11,11, 12,12,12,12,12,12 Since there is 6 occurrence for 12, and there are 5 occurrence for 3,6,10 Then it should return the 12 in cell B1, and 3 in cell C1, and 6 in cell D1, and ignore 10, because I would like to select the 3 numbers only from a list. Does anyone have any suggesitons? Thank in advance for any suggestions Eric |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find out the 3 highest occurrence of number from a list
Addendum to the instructions:
Copy C1 and paste into C2 and down as far as you need *********** Regards, Ron XL2003, WinXP "Ron Coderre" wrote: With your posted data list in A1:A36 Try this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of Enter): C1: =INDEX($A$1:$A$40,MATCH(LARGE(IF(($A$1:$A$40<""), IF(MATCH($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40), COUNTIF($A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),ROW()),IF(($A$1:$A$40<""),IF(MATCH($A$1:$A $40,$A$1:$A$40,0)=ROW($A$1:$A$40),COUNTIF($A$1:$A$ 40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),0)) Note_1: There are NO spaces in that formula Note_2: I had that formula references A1:A40 so that it includes blank cells to ensure that they don't trip up its calculation Note_3: If that formula returns the values you're looking for....we can see about making it more elegant. Does that help? *********** Regards, Ron XL2003, WinXP "Eric" wrote: Does anyone have any suggestions on how to find out the first, second, and third highest occurrence of number from a list? There is a sorted list of numbers in ascending order under column A, 3,3,3,3,3,4,4,5,5,5,6,6,6,6,6,7,7,9,9,9,10,10,10,1 0,10,11,11,11,11,11, 12,12,12,12,12,12 Since there is 6 occurrence for 12, and there are 5 occurrence for 3,6,10 Then it should return the 12 in cell B1, and 3 in cell C1, and 6 in cell D1, and ignore 10, because I would like to select the 3 numbers only from a list. Does anyone have any suggesitons? Thank in advance for any suggestions Eric |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find out the 3 highest occurrence of number from a list
Another "one more thing" : \
I just noticed that you wanted the values to list ACROSS....not Down. C1: =INDEX($A$1:$A$40,MATCH(LARGE(IF(($A$1:$A$40<""), IF(MATCH($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40), COUNTIF($A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),COLUMNS($B:B)),IF(($A$1:$A$40<""),IF(MATCH ($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40),COUNTIF( $A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),0)) Remember to commit with Ctrl+Shift+Enter Copy C1 in to D1 and E1 Using your posted data.... C1 returns 12 D1 returns 3 E1 returns 6 I hope that helps. *********** Regards, Ron XL2003, WinXP "Ron Coderre" wrote: With your posted data list in A1:A36 Try this ARRAY FORMULA (committed with Ctrl+Shift+Enter, instead of Enter): C1: =INDEX($A$1:$A$40,MATCH(LARGE(IF(($A$1:$A$40<""), IF(MATCH($A$1:$A$40,$A$1:$A$40,0)=ROW($A$1:$A$40), COUNTIF($A$1:$A$40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),ROW()),IF(($A$1:$A$40<""),IF(MATCH($A$1:$A $40,$A$1:$A$40,0)=ROW($A$1:$A$40),COUNTIF($A$1:$A$ 40,$A$1:$A$40)+1/ROW($A$1:$A$40)/1000)),0)) Note_1: There are NO spaces in that formula Note_2: I had that formula references A1:A40 so that it includes blank cells to ensure that they don't trip up its calculation Note_3: If that formula returns the values you're looking for....we can see about making it more elegant. Does that help? *********** Regards, Ron XL2003, WinXP "Eric" wrote: Does anyone have any suggestions on how to find out the first, second, and third highest occurrence of number from a list? There is a sorted list of numbers in ascending order under column A, 3,3,3,3,3,4,4,5,5,5,6,6,6,6,6,7,7,9,9,9,10,10,10,1 0,10,11,11,11,11,11, 12,12,12,12,12,12 Since there is 6 occurrence for 12, and there are 5 occurrence for 3,6,10 Then it should return the 12 in cell B1, and 3 in cell C1, and 6 in cell D1, and ignore 10, because I would like to select the 3 numbers only from a list. Does anyone have any suggesitons? Thank in advance for any suggestions Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pick highest number from a list | Excel Discussion (Misc queries) | |||
An add-in that allows you to find the highest prime in a number | Excel Discussion (Misc queries) | |||
Formula to find highest alphabetic name in a list of names | Excel Worksheet Functions | |||
i need a function to find the highest value in a list | Excel Worksheet Functions | |||
How do I find highest number in a row and change color? | Excel Worksheet Functions |