[mso] Frequency and rank of 10.000 numbers

From: "Van Baardwijk, Adri" <adri.vanbaardwijk@xxxxxxxxx>
To: "MSO Freelists" <mso@xxxxxxxxxxxxx>
Date: Thu, 3 Mar 2005 13:44:21 +0100
I had a problem. Please help.

There are 10.000 different numbers in a table: say 010203 - 383940
I need to know the Top 50 numbers with the highest frequency.
So first of all I have to start with the frequency of each number.
But I do not know how.

Thanks Ray for your solution. Great!
Sample solution sheet:
                FullList                ShortList               FreqList
RankList

                A                       B                       C
D
1               All Numbers             Unique Numbers  Frequency
Rank
2               010203          010203          3
3               010203          020405          1
4               010203          113439          6
5               020405          194142          35
6               113439          242839          7
7               113439=09
8               113439=09
9               113439=09
10              113439=09
11              113439=09
etc

1. Name your list FullList.
2. Filter the list to another column on the sheet, checking the 'unique
values only' box:=20
Data menu, Filter, Advanced Filter
Copy to another location, Copy to column Unique
Check Box Unique records only
3. Name this new list ShortList=20
4. Next to ShortList, have a column using the FREQUENCY function, =
picking up
the value next to it inShortList and counting instances in FullList=20
I have tried to use the FREQUENCY function, but I did not succeed.

Can you give me an example of a FREQUENCY function?

Instead I used the COUNTIF function:
{=3Dcountif(FullList;B2)}
But that solution consumes a lot of computing time!

5. Next to the FREQUENCY column, have a new column using the RANK =
function
to determine their relative order=20
The RANK function works perfect:
=3Drank(C2;FreqList;1)

6. Now sort FullList, ordering in ascending order by RANK

If you want to limit your rankings to the top 50 only, then instead of 5 =
and
6 you could create a new table using the LARGE funtion 50 times.

Can you give me an example of the LARGE function?
Again thank you very much Ray, I appreciate that very much.

Adri
Brussels

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************
[mso] Frequency and rank of 10.000 numbers

Other related posts:
