How do I calculate the mode for a column of text?

[matt3542]

Hi there,

I am trying to determine the most frequently occurring (mode) text entry but
my understanding is that the mode function is only used for numeric data? I
have a large dataset which lists sickness reasons and I would like to find
out the mode and return the text value to cell J163. In the example below the
mode would be "vomiting", please can anyone help, would be most appreciated,
thanks Matt

e.g

Col J

Vomiting
Vomiting
Flu symptoms
Flu symptoms
Flu
Stomach upset
Withheld
Vomiting
Sickness
vomiting

 

[Gary''s Student]

=INDEX(J1:J20,MATCH(MAX(COUNTIF(J1:J20,J1:J20)),COUNTIF(J1:J20,J1:J20),0))

This is an array formula that must be inserted with CNTRL-SHFT-ENTER rather
than just ENTER. Adjust the range to suit your needs.

 

[matt3542]

Many thanks, that worked perfectly, much appreciated
Matt

 

[T. Valko]

Here's another one.

If there will be no empty cells within the range.

Array entered** :

=INDEX(J2:J11,MODE(MATCH(J2:J11,J2:J11,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[JuanMarin]

very clever formula!

 

[RagDyer]

Here's another one that *will work* with empty spaces:

=INDEX(J1:J10,MODE(MATCH(J1:J10&"",J1:J10&"",0)))

Still an *array* formula.
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

 

[matt3542]

Hi there, this one also worked even though I had a couple of empty cells
within the range, again many thanks, a great help

 

[T. Valko]

You're welcome. Thanks for the feedback!