Results 1 to 6 of 6
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

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

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •