Excel Formulas Question

Drez

Here goes.

I'm trying to find the text that appears the most times in a column.

Let's say I have a column with three unique text values:

Drez
Poopiehead
Dancemaster
Drez
Drez
Poopiehead
Drez
Dancemaster
Drez
Drez
Drez
Poopiehead
Drez
Dancemaster
Drez

Obviously, Drez appears most often in this column.

How do I get another cell to not "total" the number of Drez counts, but to actually return "Drez" as the text value that appears most in the column?

I know I could use =COUNTIF(A1:A100,"Drez") to count how many times and even do this for all the available values and then find the max value with =MAX(blah blah blah), but I can't figure out how to use any of this to return "Drez."

Feral

Change [A1:A7] in the following formula to reflect the actual range of data that contains your list:

=INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)),COUNTIF(A1:A7,A1:A7),0))

Then copy it back into a cell on the same worksheet and hold SHIFT+CTRL as you press ENTER. (If you don't do SHIFT+CTRL+ENTER it won't work.)

Drez

All I get is #N/A and holding SHIFT-CTRL-ENTER does nothing...

What's =INDEX do? And what, exactly, does COUNTIF(Array, Array) do?

Drez

Ok, nevermind that, I see what =INDEX does. I'll screw around with the formula but yours still doesn't seem to work.

edit: I think the main problem is that your COUNTIF doesn't specify which text to look for. All it does is return a 0.

Feral

Drez wrote: »

All I get is #N/A and holding SHIFT-CTRL-ENTER does nothing...

I think I was unclear in my instructions.
Copy it back into the formula bar at the top of the Excel window, not directly into a cell, and hold SHIFT+CTRL as you press ENTER.

Drez

Huh oh shit that worked. I'm trying to understand why, but THANK YOU VERY MUCH!

Feral

Drez wrote: »

Huh oh shit that worked. I'm trying to understand why, but THANK YOU VERY MUCH!

It's an array formula. They're explained (badly) here: http://office.microsoft.com/en-us/excel/HA010872901033.aspx

Drez

Is there an easy way to get the 2nd and 3rd most frequent names?

Feral

For the second most common value, do

=INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)[b]-1[/b]),COUNTIF(A1:A7,A1:A7),0))

For the third:

=INDEX(A1:A7,MATCH(MAX(COUNTIF(A1:A7,A1:A7)[b]-2[/b]),COUNTIF(A1:A7,A1:A7),0))

And so on.

If there's a tie for any of those, it will pick the first entry in the list. For your example above, Poopiehead and Dancemaster each appear three times. If you looked for the second most common value, the formula would return Poopiehead because it appears in the list before Dancemaster.

Drez

Excellent, thank you.

I guess there's no way to resolve ties? Like if there are 8 people and 3 of them have 5 votes, I'd want them listed over anything else.

Feral

Drez wrote: »

Excellent, thank you.

I guess there's no way to resolve ties? Like if there are 8 people and 3 of them have 5 votes, I'd want them listed over anything else.

Not that I can think of without using a messy nest of IF statements.
Somebody else may have an idea, though.

Drez

Even if not, you've been a tremendous help, I cannot stress that enough.

Thank you very much!

But, yeah, if anyone has any ideas, I'm all ears.