Multiple Modes?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
Look up the FREQUENCY function in help.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Assuming that A2:A15 contains the data, try...

B2, copied down:

=IF(ISNA(MATCH(A2,$A$1:A1,0)),COUNTIF($A$2:$A$15,A2),"")

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!
 
Back
Top