Mode of Text....

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cjwenngatz@gmail.com - 25 Jan 2007 20:22 GMT

I need to pull the most commonly used word out of a data range. In
essence if I have a list of names; Is there any way Excel can pull up
the name that is repeated the most, and how many times it is repeated?
Essentially a mode of text.

Reply to this Message

Chip Pearson - 25 Jan 2007 22:06 GMT

Assuming your text values are in A1:A10, insert a new column and enter

=COUNTIF($A$1:$A$10,A1)
in cell B1 and fill down to B10.

Then use the following formula to return the value in column A that occurs
the most frequently. In case of a tie (2 different strings occur the same
number of time), the first (lowest row number) item will be returned.

=INDEX(A1:A10,MATCH(MAX(B1:B10),B1:B10,0),0)

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)

>I need to pull the most commonly used word out of a data range. In
> essence if I have a list of names; Is there any way Excel can pull up
> the name that is repeated the most, and how many times it is repeated?
> Essentially a mode of text.

Reply to this Message

Harlan Grove - 25 Jan 2007 23:41 GMT

cjwenng...@gmail.com wrote...

>I need to pull the most commonly used word out of a data range. In
>essence if I have a list of names; Is there any way Excel can pull up
>the name that is repeated the most, and how many times it is repeated?
>Essentially a mode of text.

I think Leo Heuser came up with this.

=INDEX(rng,MODE(MATCH(rng,rng,0)))

Reply to this Message