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

If you need to find the most common entry in a column where the values are text, you can use a combination of the INDEX, MATCH, and MODE functions. Here's how:

1. First, create a list of unique values in the column. To do this, select the column, go to the Data tab, and click on "Remove Duplicates". This will create a new list of unique values in the column.
   
2. Next, use the MATCH function to find the position of each unique value in the original column. For example, if your unique values are in column B and your original column is in column A, you can use the formula =MATCH(B1,A:A,0) in cell C1 and drag it down to the end of the list.
   
3. Now that you have the positions of each unique value, you can use the INDEX function to create an array of those positions. For example, if your MATCH results are in column C, you can use the formula =INDEX(C:C,MODE(C:C)) to find the most common position.
   
4. Finally, use the INDEX function again to return the corresponding text value from the original column. For example, if your original column is in column A, you can use the formula =INDEX(A:A,INDEX(C:C,MODE(C:C))) to find the most common text entry.

This formula will return the most common text entry in the column. If there are multiple entries with the same frequency, it will return the first one it encounters.

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)

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

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


--
Gary''s Student - gsnu200757


"Will" wrote:

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

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,A 2:A100,0))))

Hope this helps!

In article ,
Will wrote:

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