How to highlight consecutive duplicates but ONLY if they are a certain number/value

[dannywhite82]

Hi all,
I'm making a spreadsheet that monitors student attendance, and I want it to flag up when a student has missed 2 (and 3) consecutive classes.

e.g.
Student Name 1 1 0 0 1

(1 = attended, 0 = absent, each column represents a class)

I have tried highlighting consecutive duplicate cells as a new rule in 'conditional formatting' e.g. '=OR(B1=A1, B1=C1)' for the selected range, and this highlights 2 consecutive "0"s as I wish, BUT it also highlights 2 consecutive "1"s (which I don't want).

Does anyone have any idea how to highlight 2 consecutive 0s, discounting 1s (or other values) that are consecutive? I'm sure it must be simple but I'm a beginner at this!

I also want to highlight 3 consecutive 0s with a different colour.

Thanks,
Danny

 

[bbott]

Welcome to the board.

You can adjust your existing formula to this:

=AND(B1=0,OR(B1=A1,B1=C1))

This needs to be tweaked a bit, but the other formula (3 in a row) would look like this in C1:

=OR(SUM(A1:C1)=0,SUM(B1:D1)=0,SUM(C1:E1)=0)

 

[blbat]

excel 2010

I did this with Four conditional format rules applied to B1:F1

Cell Value = 0 Format color as Red

Cell Value = 1 Format color as Green

=COUNTIF($B$2:$F$2,B2)>=3 Format color as Red, Check STOP If True

=COUNTIF($B$2:$F$2,$B2)=2 Format color as Green


These formulas assume you have the Weekdays in Cells B1:F1, and the students name in Cell A2, the 1's and 0's in B1:F1

It will highlight any Three numbers in that week, consecutive or not.

hope that helps..or at least points you in a good direction.