Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TugboatEng on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

The Almost-Modes 3

Status
Not open for further replies.
DRWeig

DRWeig

Electrical
Joined
Apr 8, 2002
Messages
3,004
Location
US
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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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.
 
 http://files.engineering.com/getfile.aspx?folder=1d934200-9dc3-4385-af8e-161d66d2ef75&file=sort_formula_descendingV2.xlsx
Upvote 0 Downvote
That one's pretty slick, AELLC. Thanks!

Best to you,

Goober Dave

Haven't see the forum policies? Do so now: Forum Policies
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

vinodnx7
  • Locked
  • Question Question
count the same text from the file and create new list with number
Replies
19
Views
1K
vinodnx7
vinodnx7
Kenja824
  • Locked
  • Question Question
Index Match not knowing the range?
Replies
8
Views
1K
SkipVought
SkipVought
electricpete
  • Locked
  • Question Question
an update to vba routine for highlighting the current cell
Replies
6
Views
480
skeletron
skeletron
As-Lag
  • Locked
  • Question Question
Setting the path in windows 11
Replies
4
Views
494
As-Lag
As-Lag
rb1957
  • Locked
  • Question Question
where's the formula defined ?
2
Replies
20
Views
877
IDS
IDS

Part and Inventory Search

Sponsor

Back
Top