Sorted Data

  • Thread starter Thread starter Norm
  • Start date Start date
N

Norm

I have columns that have quite a bit a bit of data in them and some of the
numbers are the same.. I would like to be able to find the number that
appears most often, then the second most appearing number and third, all the
way to the fifth most appearing number. I have been able to use "MODE" to
find the most often used number but have not be able to find out how to do
the rest. Any help would be appreciated.
 
With data in say A1 to A100, enter the frequency rating in B1,
and try this:

=INDEX(A1:A100,MATCH(LARGE(FREQUENCY(A1:A100,A1:A20),B1),FREQUENCY(A1:A100,A
1:A100),0))
 
TYPO!

Try this one:

=INDEX(A1:A100,MATCH(LARGE(FREQUENCY(A1:A100,A1:A100),B1),FREQUENCY(A1:A100,
A1:A100),0))
 
I have columns that have quite a bit a bit of data in  them and some ofthe
numbers are the same..  I would like to be able to find the number that
appears most often, then the second most appearing number and third, all the
way to the fifth most appearing number.  I have been able to use "MODE"to
find the most often used number but have not be able to find out how to do
the rest.  Any help would be appreciated.
Click to expand...

try using conditionl formatting to change the color of the cell then
you can sort on cell color
 
Easiest way is to use a Pivot table.

(Excuse me if some of the vocab is not "spot on" as I'm working with non
English version and I'm trying to remember the English terms.)


Name your column of numbers "MyNumbers".
Select a Cell in the column.
Then click
menu / data / Pivot table (or Insert Pivot table in XL 2007).

Slide the MyNumbers header from the list of headers into the rows box on the
left.
Slide the MyNumbers header from the list of headers (again) in to the data
box in the table body, but then modify it from Sum to Count.

Click OK, OK, OK until the pivot table appears.

Now you have the pivot table but sorted in the wrong order.
So, Right Click on a cell in the numbers column.
Edit settings ...
Sort, from greatest to least, on the Count of numbers.
Click OK.


OK ?

ThatSaid.
 
Start with Excel 2007, and then pick, Home, Sort and filter, Custom
sort, pick Cell Color in Sort on.
 
Back
Top