Function to show the greatest number of characters

  • Thread starter Thread starter juliejg1
  • Start date Start date
J

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.
 
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.
Click to expand...


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
 
Perfect! Thank you!

Ron Rosenfeld said:
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
Click to expand...
 
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)
 
Back
Top