Excel Rank, Frequency or Count?

[eggohead]

I have a list of numbers all in the same column and need to rank the top 5 occurences of the list. How do I do this and what function(s) do I use?.... Rank, Frequency or Count or a combination? Heres what I am looking for - Thanks for your time

rank number occurrence
1 22 7
2 56 4
3 42 3
4 54 2
5 96 2

 

[GlennUK]

Have you experimented with PivotTables?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[byundt]

The brute force way to approach the problem is:
1) Enter the numbers 1 through 100 in a column. To do this easily, enter 1 and 2 in two consecutive cells. Select those cells, then drag the little square at bottom right of selection marquee down until you reach 100
2) Put the following formula next to the cell containing the 1
=COUNTIF(A:A,D1)
This formula assumes that D1 is the number 1 and your original data is in column A
3) Copy this formula down. The easy way to do that is to select the cell with the formula, then double-click the little square at bottom right of the selection marquee.
4) Select the cells containing your numbers 1 through 100 and COUNTIF formulas
5) Sort those cells using the Data...Sort menu item. Choose Descending order by the column containing your COUNTIF formula.

Brad

 

[byundt]

If you want to try Glenn's suggestion of a PivotTable, you'll find that it has an option to display the values with the top 5 counts. If you're not familiar with PivotTables, you may find the instructions below to be helpful.

Creating a PivotTable can be somewhat challenging because of the unfamiliar nature of the wizard. To create a PivotTable:
1) Make sure that each column has a heading label, then select any point within your data. I'll assume that you have labelled your data "MyData"
2) Open the Data...PivotTable and PivotChart Report menu item
3) Click Finish in the resulting wizard. This will create a PivotTable on a new worksheet. Or you can click "Next" twice and choose to put the PivotTable in the existing worksheet in step 3 of the wizard. You'll then need to pick the top left cell of that report.

The tricky part come next. You will be presented with a blank PivotTable with big labels for the type of data required in each section. You need to drag named fields from the "PivotTable field list" dialog onto the PivotTable.
4) Drag the "MyData" field label into the "Drag Row Items Here" field
5) Drag the "MyData" field label into the "Drag Data Items Here" field
6) Right-click one of the cells in the first column of your PivotTable and choose "Field settings" from the resulting pop-up.
7) Choose to summarize by "Count" instead of "Sum"
8) Click the "Advanced" button
9) Choose the option to display the "Top 10". Then choose 5 in the dropdown field below that option, and "Count of MyData" in the other dropdown at the bottom right.
10) Choose to sort the data in Descending order, then specify "Count of MyData" in the dropdown field underneath that option.


You now have a PivotTable showing the counts of the five most frequent numbers in your original data.

Brad