The Almost-Modes 3

[DRWeig]

Hi all,

I'm scratching my head on this one. I have a column of numbers (30,000+). I can easliy find what number occurs more often than any other with the =MODE(x:y) formula.

What I'd like to know are the numbers that occur second most, third most, etc...

Any quick way to do this without getting into VB?

Best to you,

Goober Dave

Haven't see the forum policies? Do so now: Forum Policies

 

[cowski]

Perhaps a histogram would help?

http://peltiertech.com/Excel/Charts/Histograms.html

 

[zelgar]

Probably not the most efficient way, but you can do the following:
Assuming the data is in Cells A1 - A10 & cell E1 =Mode(A1:A10)
1. Insert in the cells in Cell B1 =IF(A1<>$E$1,A1,"") Copy & paste down to B10
2. In Cell E2 = Mode(B1:B10) (This gives you the Second most)
3. In Cell C1 =IF(B1<>$E$2,B1,"") Copy & paste down to C10
3. In Cell E3 = Mode(C1:C10) (this gives you the 3rd most

 

[AELLC]

Try attached file

 

[MintJulep]

Try the FREQUENCY() function.

 

[DRWeig]

Thanks all!

I settled in on a function with FREQUENCY() and a simple sort. Lucky for me, all the numbers are from 1 to 100 inclusive.

Thanks cowski, a histogram would be cool but too wide to grab data from easily.

Thanks zelgar, your way is foolproof and it's the one I did first on this set of data. I used it as a check against what I got with frequency - sort. I hate to have a manual sort take place, but it's a quick one. Without it (or a massively complex statement with COUNTIFs INDEXs and MATCHs) I can't flag two numbers with equal frequencies. Even with zelgar's method, I have to extend it downward a bunch in case there's a 10-way tie for third place.

Solved!

Best to you,

Goober Dave

Haven't see the forum policies? Do so now: Forum Policies

 

[DRWeig]

Forgot! AELLC - you lost me in your "sorted result" column. I can get the count of each possible number with COUNTIF or FREQUENCY, and I get the RANK function, but your column F left me brain-fried.

Best to you,

Goober Dave

Haven't see the forum policies? Do so now: Forum Policies

 

[AELLC]

Doesn't the new MODE.MULT function do this easier?

It works in my Excel 2010 but I don't know in what version of Excel it first appeared.

 

[AELLC]

I tried using the MODE.MULT function, and it seemed to get nowhere.

Attached is an improved version, no sorting required, and it will be further improved when I get the time to flag ties for any ranking.

 

[DRWeig]

That one's pretty slick, AELLC. Thanks!

Best to you,

Goober Dave

Haven't see the forum policies? Do so now: Forum Policies