MODE function for text entries

W

Will

I need to find the most common entry in a column where the values are text.
MODE only works on numeric values.
 
R

Ron Coderre

With a list in A1:A20

Try something like this (in segments for readability):
B1:
=INDEX(A1:A20,
MATCH(MAX(INDEX(COUNTIF(A1:A20,A1:A20),0)),
INDEX(COUNTIF(A1:A20,A1:A20),0),0))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)
 
G

Gary''s Student

say the data is in column B.

In A1 enter:
=COUNTIF($B$1:$B$16,B1)
and copy down

In another cell enter:
=VLOOKUP(MAX(A1:A16),A1:B16,2)
This is like MODE because it is the most frequent. For example:

1 bird
8 dog
4 cat
2 mouse
4 cat
4 cat
8 dog
8 dog
1 rate
8 dog
2 mouse
8 dog
8 dog
8 dog
8 dog
4 cat

and the formula returns:
dog
 
D

Domenic

Assuming that A2:A100 contains the data, try the following formula that
needs to be confirmed with CONTROL+SHIFT+ENTER...

=INDEX(A2:A100,MODE(IF(A2:A100<>"",MATCH(A2:A100,A2:A100,0))))

Hope this helps!
 
Top