Conditional formatting- multiple conditions

Hi all. I am completely new to this forum and don't have super Excel skills but I have been attempting, without success, to perform conditional formatting that will act on only non-blank cells in a range and then change any of those cell's formatting (fill or font color) if the value within that range does not equal a value in another cell. It sounds simple enough and I have been trying but again, not really all that savvy! I tried nesting an ISBLANK with <> but to no avail.

This is for managing third grade test scores. I have a sample image below.

• The total possible points for this problem is 2.
• If a student gets a 2, I'd like to leave that cell alone.
• If the student gets a 1 or a zero, I'd like the cell's outline or fill to change to red to reflect the fail and then be able to count the number of failed questions at the bottom to do a percentage of students who have failed a particular question.
• Finally, if a cell is blank, I need to ignore it until the teacher eventually enters a 0, 1 or 2.

Capture.PNG

Thanks you in advance for any tips!

Select your Pts range (I used column B)

in CF

Use formula .....

=AND($B2<>"",$B2<2)

For Percentage

=COUNTIF($B$2:$B$100,"<2")/COUNT($B$2:$B$100)

Thank you, John!

Thank you, Richard!

Thank you, Ali!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Done. Thanks John.