Function to show the greatest number of characters

[juliejg1]

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.

 

[Ron Rosenfeld]

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)),COUNTIF(A1:F1,A1:F1),0)))


--ron

 

[juliejg1]

Perfect! Thank you!

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)),COUNTIF(A1:F1,A1:F1),0)))


--ron

 

[Chip Pearson]

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)

 

[Ron Rosenfeld]

Perfect! Thank you!

You're welcome.
--ron