Excel formula that can identify 'mixed' sequences of binomially coded data?

user_13844273 0
Viewed 105

Any help would be hugely appreciated!

If I have the following rows of data in Excel (5 examples below), whereby data is coded as 1 or 2, does anyone know an Excel formula that can identify/highlight the rows that contain a mixture of 1's and 2's?

I am only interested in sequences that have a mixture of 1's and 2's.

For example;

1, 2, 2, 2, 2, 1, 2, 2, 2 (this sequence has a mixture)

1, 1, 1, 1, 1, 1, 1, 1, 1. (this sequence does not have a mixture)

1, 2, 2, 2, 1, 1, 2, 2, 1. (this sequence has a mixture)

2, 2, 2, 2, 2, 2, 2, 2, 2. (this sequence does not have a mixture)

1, 1, 2, 2, 1, 1 , 2, 1, 1. (this sequence has a mixture)

Currently I use the following formula to highlight/identify the non-mixed sequences with only 1's or 2's;

=IF(ISERROR(FIND("222222222",TEXTJOIN("",TRUE,A1:A9))),FALSE,TRUE)

=IF(ISERROR(FIND("111111111",TEXTJOIN("",TRUE,A1:A9))),FALSE,TRUE)

But now I am in search of a formula that can bring up the mixture sequences.

excel-formula excel
5 Answers

If you definitely want to identify rows containing a mixture of 1's and 2's, you could use countifs like this:

=AND(COUNTIF(A1:J1,1),COUNTIF(A1:J1,2))

enter image description here

If you wanted to be more concise, you could use

=VAR(A1:I1)>0

(All the answers solve the problem as stated. You could argue that my original answer is robust in the sense that it would cope with blank cells and out-of-range values like 0 or 3)

user_38949170

One more option, for pre O365 - entered as array formula:

=OR(A1:H1<>I1)

Array formula after editing is confirmed by pressing ctrl + shift + enter

enter image description here

user_121888610

In J1 formula copied down :

=SUMPRODUCT(A1:I1-I1)<>0

enter image description here

Edit :

Explanation of the formula logic

Take this part of formula to check :

In K1, enter :

=SUMPRODUCT(A1:I1-I1) become >>

=SUMPRODUCT({1,2,2,2,2,1,2,2,2}-2) become >>

=SUMPRODUCT({-1,0,0,0,0,-1,0,0,0}) become >>

=-2

And

In K2, enter :

=SUMPRODUCT(A2:I2-I2) become >>

=SUMPRODUCT({1,1,1,1,1,1,1,1,1}-1) become >>

=SUMPRODUCT({0,0,0,0,0,0,0,0,0}) become >>

=0

Then

Since the range only have 2 number (1 or 2),

if the range minus any one number,

the range (all have the same number) the total sum must equal to "0",

else, "not 0"

Follow this logic, you can have another solution similar to Tom Sharpe's suggestion formula in using COUNTIF function

=COUNTIF(A1:J1,I1)<>COUNT(A1:J1)
user_121251810

Alternatively maybe:

enter image description here

Formula in J1:

=MOD(SUM(A1:I1),9)>0

Or:

=COUNTIF(A1:H1,I1)<8

Or:

=SUM(A1:I1)<>I1*9

Or:

=STDEV(A1:I1)>0

As a conditional formatting formula I selected A1:I7 and, for example, used:

=STDEV($A1:$I1)>0
user_97581940

If you have O365, you could use:

=COUNT(UNIQUE(A1:I1,TRUE))=2

will return TRUE if mixed, else FALSE

user_28729220
Related