Mix of values in rows

[Urban]

Is there a way to write a single formula that returns true if any row in an array has a mix of numeric and alpha values?

Example that returns false:
0, 1, 2
a, b, c

Example that returns true:
3, 4, d
e, f, g

 

[Cindy Ellis]

Hello Urban, and welcome to the Board!
There are likely many ways to solve this problem in Excel, so others will likely chime in with their approach...here's one that uses the distinction between Count() and Counta().
Since your example uses 3 values, but your question was more general, the formula given is adaptable to any range of cells without modification other than changing the range reference in the 4 locations.

=IF(OR(COUNT(E3:G3)= COUNTA(E3:G3),AND(COUNT(E3:G3)=0,COUNTA(E3:G3)>0)),"Uniform","Mixed")

The formula above is for data in cells E3:G3...but could be any range that you can populate into the 4 spots in the formula.
Hope that helps,
Cindy
Just noticed that you need True if it's mixed, false otherwise...I'll tweak the formula and post back.

 

[Cindy Ellis]

OK...this reverses the logic (and tests it on a larger range, E3:I3):

=IF(AND(COUNT(E3:I3)<> COUNTA(E3:I3),COUNT(E3:I3)<>0),TRUE,FALSE)

Hope that works for you,
Cindy

 

[Urban]

Thanks for the reply. Initially I thought that what you had was what I wanted, but looking again I don't think so.

The data I have is not a single row. I have multiple rows and I am trying to check if any single row in the set of rows I am evaluating has a mix of alpha and numeric values.

Example #1:
0, 1, 2
a, b, c
This example should return false because neither row has a mix of alpha and numerics. The fact that row #1 is all numeric and #2 is all alpha is fine.

Example #2:
3, 4, d
e, f, g
This example should return true because the first row has a mix of alpha and numeric. Row #2 is all alpha which is good, but row #1 isn't so overall the two rows fail.

I was able to add a true/false formula to each row and then one cell for the whole block that checks if any row returns true and if so the whole thing is true. I also created a VBA option. But I would like to do this all with a formula in one cell if possible.

Thanks for the help.

 

[Cindy Ellis]

Is it always just 2 rows, with a variable number of columns? Or are you looking for a formula that would be readily adapted to multiple rows (more difficult, but will probably require a different approach)?

 

[Urban]

It is a variable number of rows and a fixed number of columns.