×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# The Almost-Modes3

## 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?

Best to you,

Goober Dave

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

### RE: The Almost-Modes

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

### RE: The Almost-Modes

Try the FREQUENCY() function.

### RE: The Almost-Modes

(OP)
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

### RE: The Almost-Modes

(OP)
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

### RE: The Almost-Modes

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.

### RE: The Almost-Modes

(OP)
That one's pretty slick, AELLC. Thanks!

Best to you,

Goober Dave

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

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Close Box

# Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!