Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Multiple Modes?
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! |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Multiple Modes?
Look up the FREQUENCY function in help.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Gaeza" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro: Filter Multiple header then extract to Multiple Files | Excel Discussion (Misc queries) | |||
Display multiple lines of text within a cell from multiple column. | Excel Worksheet Functions | |||
switching between modes | Excel Discussion (Misc queries) | |||
view multiple files in multiple windows on multiple screens. | Excel Discussion (Misc queries) | |||
Design/run modes for a button | Excel Discussion (Misc queries) |