Conditional Formatting Help

[DushiPunda]

Hello,
Looking for some help with CF. My spreadsheet is used to track monthly exams for each of my workers. Here's a truncated layout:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: center"]Apr[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Worker1[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]100
[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Worker2
[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Worker3
[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"][/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Worker4[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Average[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]100[/TD]
[/TR]
</tbody>[/TABLE]

Average formula:

=IFERROR(AVERAGE(B2:B5),0)

Then I have this CF formula to highlight tests that don't have a score yet but only if at least one other score has been input for that month:

Applies to: B2:M15
Formula: =AND(B$6<>0,B2="")
Formatting: Red Fill

I want to expand this, or add another CF rule if required, to make the name of any worker who gets hit on the above CF rule red (red text).

So, looking at the table above. The two blank cells (D4 & E3) would currently be filled red with the above CF rule. With the new rule, Worker2 & Worker3 text would be changed to red. Hopefully that explains what I'm trying to do. Thank you!

 

[Marcelo Branco]

Maybe...

Select A2:A5 and use this formula in CF
=COUNTIF($B2:$M2,">0")<>COUNTIF(B$6:M$6,">0")
pick the format you want

M.

 

[DushiPunda]

Thank you Marcelo. I was able to get my desired effect with two rules. I had to use two because there are other possible values in the cells (TRNG, Exempt, -) and some of that is pre-filled out for the entire year, which would result in a 12<>10 in those cases. Here's what I ended up doing:

#1 :
Applies to: A4:A15
Formula: =COUNTIF($B4:$M4,">0")+COUNTIF($B4:$M4,"TRNG")+COUNTIF($B4:$M4,"Exempt")+COUNTIF($B4:$M4,"-")<>COUNTIF(B$17:M$17,">0")
Format: Fill RED

#2 :
Applies to: A4:A15
Formula: =COUNTIF($B4:$M4,">0")+COUNTIF($B4:$M4,"TRNG")+COUNTIF($B4:$M4,"Exempt")+COUNTIF($B4:$M4,"-")=12
Format: No Format Set

Obviously the second rule comes before the first rule. Thanks again!

 

[Marcelo Branco]

You are welcome. Great you figured out formulas that work.

M.