Results 1 to 6 of 6

20060111, 20:31 #1
 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!

20060111, 20:47 #2
 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.

20060111, 20:56 #3
 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!

20060111, 21:05 #4
 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 nonblank entries?

20060111, 21:06 #5
 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

20060111, 21:14 #6
 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.