Results 1 to 5 of 5

Conditional Formatting for Standard Deviation

Threaded View

  1. #1
    Forum Contributor
    Join Date
    01-09-2009
    Location
    NJ/NY
    MS-Off Ver
    Excel 2007 & 2016
    Posts
    124

    Conditional Formatting for Standard Deviation

    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.
    Attached Files Attached Files
    Last edited by OverKnight; 01-16-2013 at 12:58 PM. Reason: Trying to make it a little clearer...

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1