The Almost-Modes
The Almost-Modes
(OP)
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?
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





RE: The Almost-Modes
http://peltiertech.com/Excel/Charts/Histograms.htm...
RE: The Almost-Modes
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
RE: The Almost-Modes
RE: The Almost-Modes
RE: The Almost-Modes
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
RE: The Almost-Modes
Best to you,
Goober Dave
Haven't see the forum policies? Do so now: Forum Policies
RE: The Almost-Modes
It works in my Excel 2010 but I don't know in what version of Excel it first appeared.
RE: The Almost-Modes
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.
RE: The Almost-Modes
Best to you,
Goober Dave
Haven't see the forum policies? Do so now: Forum Policies