I have the following in cells:
A1 = blank
B1 = C
C1 = D
D1 = C
E1 = blank
F1 = C

I need a formula in G1 that will show what character (or blank) is the most
common in the range A1:F1. In the case above G1 should show 'C'. If there
are more blank cells in the range then G1 should be blank.

On Tue, 11 Dec 2007 12:45:00 -0800, juliejg1
wrote:

I have the following in cells:
A1 = blank
B1 = C
C1 = D
D1 = C
E1 = blank
F1 = C

I need a formula in G1 that will show what character (or blank) is the most
common in the range A1:F1. In the case above G1 should show 'C'. If there
are more blank cells in the range then G1 should be blank.


Array-Entered (i.e. enter with <ctrl<shift<enter. Excel should place braces
{...} around the formula):

=IF(COUNTBLANK(A1:F1)MAX(COUNTIF(A1:F1,A1:F1)),"" ,
INDEX(A1:F1,MATCH(MAX(COUNTIF(A1:F1,A1:F1)),COUNTI F(A1:F1,A1:F1),0)))


--ron

Perfect! Thank you!

"Ron Rosenfeld" wrote:

On Tue, 11 Dec 2007 12:45:00 -0800, juliejg1
wrote:

I have the following in cells:
A1 = blank
B1 = C
C1 = D
D1 = C
E1 = blank
F1 = C

I need a formula in G1 that will show what character (or blank) is the most
common in the range A1:F1. In the case above G1 should show 'C'. If there
are more blank cells in the range then G1 should be blank.


Array-Entered (i.e. enter with <ctrl<shift<enter. Excel should place braces
{...} around the formula):

=IF(COUNTBLANK(A1:F1)MAX(COUNTIF(A1:F1,A1:F1)),"" ,
INDEX(A1:F1,MATCH(MAX(COUNTIF(A1:F1,A1:F1)),COUNTI F(A1:F1,A1:F1),0)))


--ron

Try the following array formula:

=IF(COUNTA($A$1:$F$1)=0,"No
Data",OFFSET($A$1,0,MATCH(MAX(COUNTIF($A$1:$F$1,A1 :F1)),COUNTIF($A$1:$F$1,A1:F1),0)-1))

Since this is an array formula, you must press CTRL SHIFT ENTER rather than
just ENTER when you first enter the formula and whenever you edit it later.
If you do this properly, Excel will display the formula in the formula bar
enclosed in curly braces { }. See www.cpearson.com/Excel/ArrayFormulas.aspx
for more information about array formulas.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




"juliejg1" wrote in message
...
I have the following in cells:
A1 = blank
B1 = C
C1 = D
D1 = C
E1 = blank
F1 = C

I need a formula in G1 that will show what character (or blank) is the
most
common in the range A1:F1. In the case above G1 should show 'C'. If
there
are more blank cells in the range then G1 should be blank.

On Tue, 11 Dec 2007 14:12:09 -0800, juliejg1
wrote:

Perfect! Thank you!

You're welcome.
--ron