+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting for Standard Deviation

  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...

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

    Re: Conditional Formatting for Standard Deviation

    I hope it's okay to bump this back up to the top. No one wants to take a crack at this one? I'd really appreciate some guidance!

  3. #3
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Conditional Formatting for Standard Deviation

    the lowest condition is red,orange,yellow
    i used the cf->formula
    like
    =OR(C8<($C$3-($C$4*3)),C8>($C$3+($C$4*3)))
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

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

    Re: Conditional Formatting for Standard Deviation

    Thank you, Vlady. This works exactly as I wanted. Thanks for taking the time to help me.

  5. #5
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Conditional Formatting for Standard Deviation

    your very much welcome..

    regards,
    vladimir

+ Reply to Thread

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