# Thread: Something like MODE for text values? (2002-XP)

1. ## Something like MODE for text values? (2002-XP)

I'm trying to find the "most popular" (most frequent) answer in a chart of multiple-choice answsers among 26 students on 14 questions. Sometimes, it's obvious, since 80% of the class got that one right (calculated using a DCOUNTA/COUNTA formula), but in other cases, it's a real mix.

MODE sounds like the ticket, but it does not seem to handle anything except numbers. For a range filled with the letters A through E, it return "N/A". Of the other statistical functions, which I admit I don't fully understand, none seem to find the most frequently occuring value.

Does anyone have an easy solution? Please don't spend a lot of time writing code for this, as I could do it manually in 3 minutes. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> But it sure would be nice of Excel did it automatically.

2. ## Re: Something like MODE for text values? (2002-XP)

Say the answers are in A1:A100. Use the formula

=CHAR(64+MODE(CODE(A1:A100)-64))

3. ## Re: Something like MODE for text values? (2002-XP)

Very clever. From what I can see, it works perfectly. Muchas gracias.

4. ## Re: Something like MODE for text values? (2002-XP)

In fact, the 64+ and -64 aren't necessary, so the formula can be even simpler. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

5. ## Re: Something like MODE for text values? (2002-XP)

The formula Hans supplied will not account for multiple winners. If both A and E have a high of 10 responses, whichever letter is encountered first in the range will be selected.
Try this formula, again if the range is A1:A100

="A-"&COUNTIF(A1:A100,"A")&"/ B-"&COUNTIF(A1:A100,"B")&"/ C-"&COUNTIF(A1:A100,"C")&"/ D-"&COUNTIF(A1:A100,"D")&"/ E-"&COUNTIF(A1:A100,"E")

It will give you the count for each letter in one cell. Hope it helps.

yoyoPHIL

6. ## Re: Something like MODE for text values? (2002-XP)

I've attached an example that is more generalized which might be useful. It uses conditional formats to highlight the 3 most popular answers for each question, each in different colors.

#### Posting Permissions

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