I am trying to use conditional formatting to change the color of cell entries for an ongoing quality control program; a sample sheet is attached. Baseline data have been established (C3:D5), and ongoing data (E3:H5) are compared with these. I want the ongoing entries in C8:D37 to change color if they’re outside of +/- 1 (yellow), +/- 2 (orange) or +/- 3 (red) SD.
I’ve set up three rules, one each for color. I used “Format only cells that contain”, “Cell Value” and “not between”. I used these formulae:
+/- 1 SD: "=$C$3-$C$4" and "=$C$3+$C$4"
+/- 2 SD: "=$C$3-($C$4*2)" and "=$C$3+($C$4*2)"
+/- 3 SD: "=$C$3-($C$4*3)" and "=$C$3+($C$4*3)"
"Stop If True" is checked off for the +/- 1 and +/- 2 SD rules.
I set this up after five sets of data were entered, and it worked with these data. However, when I tested it with additional data, the cells only turn yellow, not orange or red. I’ve included the data ranges for each parameter in columns J:N. Can anyone show me what’s wrong?
Thank you.
Bookmarks