Formula for returning most common "word" in a row or column

**Moshsoft** · 04-13-2009, 05:53 AM

I'm have a simple spread sheet that includes days and dates. I have a countif formula counting the individual days (as in: Monday = 5, Tuesdays = 2, etc...) I want a formula that returns the most frequent day in text format.

**daddylonglegs** · 04-13-2009, 06:06 AM

Let's assume that your days ("Monday", Tuesday" etc. in text format) are in the range B2:B100 then you can use this formula to get the most common directly

=INDEX(B2:B100,MODE(IF(B2:B100<>"",MATCH(B2:B100,B2:B100,0))))

confirmed with CTRL + SHIFT +ENTER

although if there is a tie this will simply return the one that occurs first

You say you have a count of each day, so if you have each day listed in F2:F8 and the count of each (with COUNTIF formula) in G2:G8 then you can get the most common from that data with this formula

=INDEX(F2:F8,MATCH(MAX(G2:G8),G2:G8,0))

again, if there's a tie you'll only get the first one listed....

**Moshsoft** · 04-13-2009, 06:12 AM

Excellent,

I'm using two versions of excel, one at home (2003) and at work, which I believe is XP 2000. Will this work on both?

**daddylonglegs** · 04-13-2009, 06:15 AM

Yes, those are standard functions. Definitely works on 2003 and I'm 99.9% sure it'll work on Excel 2000 also, although I don't have that version so I can't test and give you a definitive "Yes"......

**Moshsoft** · 04-13-2009, 06:26 AM

Great, I've tried the =INDEX(B2:B100,MODE(IF(B2:B100<>"",MATCH(B2:B100,B2:B100,0))))
But so far it's just crashed my Excel..?

Does it have limitations, is this because the source is b4:b65000..?

**daddylonglegs** · 04-13-2009, 06:29 AM

There's no specific limit for that formula but with that much data I'm not surprised it caused you problems.

The second approach is much more efficient if you have that much data......