# Thread: Finding the 'modal' text value (2002)

1. ## Finding the 'modal' text value (2002)

Is it possible to find the most frequently occurring text value in a column using a simple formula? The 'Mode' function doesn't work with text.

Alternatively, how could I extract this value from a Pivot Table? Thanks, Andy.

2. ## Re: Finding the 'modal' text value (2002)

This may be a long way round, but...

in another column, use a COUNTIF formula to find the number of occurrences of the text in question. Then use MAX to find the maximum number of occurrences. Then you can use a LOOKUP formula to find the corresponding text.

See attached example.

3. ## Re: Finding the 'modal' text value (2002)

I odn't sknow if this is "simple enough" but if your text is in A1:A100 this should work:

=OFFSET(A1,MIN(IF(COUNTIF(\$A\$1:\$A\$100,A1:A100)=MAX (COUNTIF(\$A\$1:\$A\$100,A1:A100)),ROW(A1:A100)))-1,0)

If there is more than 1 mode it will find the one whose first entry has the lowest row number (ie first seen in the list)

Steve

4. ## Re: Finding the 'modal' text value (2002)

Assuming your range is named data, this array-formula will return the most frequent text (or the first if more than one value appears the same number of times):
=INDEX(data,MATCH(MAX(COUNTIF(data,data)),COUNTIF( data,data),0))
(press Ctrl+Shift+Enter to enter)
Or with a pivot table, put the text field into the row field area, then also into the data area.Then right-click the field in the row area, choose Advanced, then select Top 1.
HTH

5. ## Re: Finding the 'modal' text value (2002)

Thank you everyone. All suggestions have been helpful. I particularly like the Pivot Table, and 'Top 1' idea.
Andy.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•