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
|