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

1. ## 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. ## 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. ## 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. ## 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. ## Re: Create a Mode Formula for Text Values (Excel 9

Ignore the blank ones.

Thanks

6. ## 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
•