Multiple Modes?
Assuming that A2:A15 contains the data, try...
B2, copied down:
=IF(ISNA(MATCH(A2,$A$1:A1,0)),COUNTIF($A$2:$A$15,A 2),"")
C2, copied down:
=IF(N(B2),RANK(B2,$B$2:$B$15)+COUNTIF($B$2:B2,B2)-1,"")
D1: 10
(This indicates that you want a Top 10 list. Change this accordingly.
For example, change this to 5 for a Top 5 list.)
E1:
=MAX(IF(B2:B15=INDEX(B2:B15,MATCH(D1,C2:C15,0)),C2 :C15))-D1
....confirmed with CONTROL+SHIFT+ENTER.
F2, copied down:
=IF(ROWS(F$2:F2)<=$D$1+$E$1,INDEX($A$2:$A$15,MATCH (ROWS(F$2:F2),$C$2:$C$1
5,0)),"")
Hope this helps!
In article ,
Gaeza wrote:
I cant figure out how to get, let's say the top 10 recurring values for a
very large set of data. I need to know which values occur most frequent. I
have been able to get the mode but not the top ten modes. Your help will be
appreciated. Thanks!
|