Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jan 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create a Mode Formula for Text Values (Excel 97)

    I have a list of cells with either A, B, C or D. I want to know which letter appears most often. I tried using MODE but it returned N/A. Is there something I can use for text? I don't need to know the number of As, Bs, etc, just which on appeared the most often.

    Thanks!

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

    Re: Create a Mode Formula for Text Values (Excel 97)

    Say the list is in A1:A100. Use this formula:
    <code>
    =CHAR(MODE(CODE(A1:A100)))
    </code>
    CODE returns the ANSI code of the characters, i.e. a number in the range 0 - 255. You can calculate the MODE of these numbers. CHAR converts this back to the corresponding character.

  3. #3
    New Lounger
    Join Date
    Jan 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a Mode Formula for Text Values (Excel 9

    That would work, except I just realized that I have a few cells with no A, B, C or D. Where nothing was answered. How do I get around that? It gave me a result of #VALUE!

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

    Re: Create a Mode Formula for Text Values (Excel 9

    What if the number of blanks is the highest? Do you want the result to be "blank", or the modus of the non-blank entries?

  5. #5
    New Lounger
    Join Date
    Jan 2006
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create a Mode Formula for Text Values (Excel 9

    Ignore the blank ones.

    Thanks

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

    Re: Create a Mode Formula for Text Values (Excel 9

    Try this. It's an array formula, i.e. you must confirm it with Ctrl+Shift+Enter instead of just Enter:
    <code>
    =CHAR(MODE(IF(A1:A100="","",CODE(A1:A100))))
    </code>
    The formula uses the fact that MODE ignores text values.

Posting Permissions

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