I'm out of idea how I could format consecutive same (respectively only even) values in Excel tables without using VBA. The conditional formatting shall color only consecutive values and only all 0s or all even values, when there are not more than 2.

A: ID	B: binary	C: counting
1	1	1
2	0	2
3	0	2
4	1	3
5	0	4
6	0	4
7	0	4
8	1	5
9	1	5

I tried to format with: =COUNTIF(C1:C9, C1) < 3, but then it also colors the 1s and C6:C7, eventho there are more than 2.

I also tried =AND( COUNTIF(C1:C9,C1) < 3, ISEVEN(C1:C9) ) but then it colors nothing. I could replace the 0s with empty cells so I could check ISEMPTY(B1:B9) but it again colors nothing. Using $ to set absolute changes nothing as well. Formating duplicates also colors triplets, which also doesn't work for me.

=OR(COUNTIF($C$1:$C$9,C1) = 1, COUNTIF($C$1:$C$6,C1) = 2) works so far, but also colors the 1s (uneven). =AND(OR(COUNTIF($C$1:$C$9,C1) = 1, COUNTIF($C$1:$C$6,C1) = 2), ISEVEN($C$1:$C$9)) doesn't work.

=AND(OR(COUNTIF($C$1:$C$9,C1) = 1, COUNTIF($C$1:$C$6,C1) = 2), $B$1:$B$9 <> 1) doesn't work as well.

My only solution so far is using 2 formating rules:

1. color =OR(COUNTIF($C$1:$C$9,C1) = 1, COUNTIF($C$1:$C$6,C1) = 2)
2. do not color =$B$1:$B$9 = 1

but I think it is terrible. I worked on it for some hours, maybe I'm missing something really obvious.

I'm not allowed to use VBA, therefore this is ot an option.

EDIT: My 2.rule-solution can be simplificed with:

1. color =COUNTIF($C$1:$C$9,C1) < 3
2. do not color =$B$1:$B$9 = 1

I'm still confused why combining both doesn't work: AND(COUNTIF($C$1:$C$9,C1) < 3; $B$1:$B$9 <> 1)

EDIT2: I know why it didn't work. Don't check <>1 with absolute value-range $B$1$:$B$9 Solution: B1 <> 1 then it loops through.

Now combining both works:

=AND( COUNTIF($C$1:$C$9, C1) < 3, B1 <> 1)

CodePudding user response：

I can't see an easy answer for the binary numbers. You have two cases:

(1) Current cell is zero, previous cell is 1, next cell is zero and next cell but one is 1.

(2) Current cell is zero, previous cell is zero, previous cell but one is 1, next cell is 1.

But then the first pair of numbers is a special case because there is no previous cell.

Strictly speaking the last pair of numbers is a special case as well because there is no following cell.

=OR(AND(ROW()=1,B$1=0,B$2=0,B$3=1),AND(ROW()=2,B$1=0,B$2=0,B$3=1),AND(B1=0,B1048576=1,B2=0,B3=1),AND(B1=0,B1048576=0,B1048575=1,B2=1))

where I have used the fact that you are allowed to wrap ranges to the end of the sheet (B1048576) in conditional formatting.

Adding the condition for the case where there there are two zeroes at the end of the range:

=OR(AND(ROW()=1,B$1=0,B$2=0,B$3=1),
AND(ROW()=2,B$1=0,B$2=0,B$3=1),
AND(B1=0,B1048576=1,B2=0,OR(B3=1,B3="")),
AND(B1=0,B1048576=0,B1048575=1,OR(B2=1,B2="")))

Even this could go wrong if there was something in the very last couple of rows of the sheet, so I suppose to be absolutely safe:

=OR(AND(ROW()=1,B$1=0,B$2=0,B$3=1),
AND(ROW()=2,B$1=0,B$2=0,B$3=1),
AND(Row()>1,B1=0,B1048576=1,B2=0,OR(B3=1,B3="")),
AND(Row()>2,B1=0,B1048576=0,B1048575=1,OR(B2=1,B2="")))

Shorter:

=OR(AND(ROW()<=2,B$1 B$2=0,B$3=1),
AND(B1 B2=0,B1048576=1,OR(B3=1,B3="")),
AND(B1 B1048576=0,B1048575=1,OR(B2=1,B2="")))

CodePudding user response：

Not the cleanest wat but it works. You only need to move your data 1 row below, so headers would be in row 2 and data in row 3 for this formula to work:

=IF(AND(B3=B4,B3<>B5),IF(AND(B4=B3,B4<>B2),TRUE,FALSE),IF(AND(B3=B2,B3<>B1),IF(AND(B3=B4,B3<>B5),FALSE,TRUE),FALSE))

CodePudding user response：

How about this approach (Office 365):

=LET(range,B$1:B$9, 
     s,IFERROR(TRANSPOSE(INDEX(range,ROW() SEQUENCE(5,,-2))),1),
     t,TEXTJOIN("",,(s=INDEX(range,ROW()))*ISEVEN(s)),
IFERROR(SEARCH("0110",t)<4,IFERROR(SEARCH("010",t)=2,FALSE)))

It creates an array s of 5 values starting point is the current row of the range, adding the 2 values above and below. If the value is out of range it will replace the error with a 1.

The array s is checked for being even (TRUE/FALSE, IFERROR created values are uneven) and the values to equal the value of the current row of the range (TRUE/FALSE).

These two booleans are multiplied creating 1 for both values being TRUE, else 0.

These values are joined and checked for 2 consecutive 1's (surrounded by 0) to be found in the 2nd or 3rd position of the range (this would be the case if two even consecutive equal numbers are found), if it errors it will look if a unique even number is found (1 surrounded by 0 in 2nd position).

PS I'm unable to test if conditional formatting allows you to type the range as B:B instead of B$1:B$9 (working from a mobile) but that would make it more dynamical, because that way you can easily expand the conditional range.