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

Topic

New Reply

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. But it sure would be nice of Excel did it automatically.

Reply | Quote

Replies

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

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

Reply | Quote

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

Reply | Quote

In fact, the 64+ and -64 aren’t necessary, so the formula can be even simpler.

Reply | Quote

In fact, the 64+ and -64 aren’t necessary, so the formula can be even simpler.

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote