Conditional Formatting with Standard Deviations

**ajzeleny** · 08-20-2014, 11:27 AM

Need some assistance with conditional formatting and how to change color of the cells that are between the average and 1 standard deviation. The rules that I applied are 1. 1 standard deviation above change to Yellow, 2. 2 standard deviation above change to Orange, 3. 3 standard deviation above change to red 4. equal and below change to green. However when I apply these rules then everything within the average and 1 standard deviation remains white. I have attached my excel file for assistance. Any help you could provide would be greatly appreciated!!!

**ben_hensel** · 08-20-2014, 11:37 AM

That's becuase you don't have a rule that covers the range between the mean and mean+1StDev.

Since you're just formatting "everything else" as green compared to the rules, just format them green if the cells contain numbers or something, or manually format the range green and use rules to change it to yellow/orange/red, and get rid of the fourth rule.

**GeneralDisarray** · 08-20-2014, 11:42 AM

Do you mean you want everything that is less than 1 standard deviation above the average to be green? Simple solution would be to just set the default fill to Green (not white) and then let the conditional formatting change to yellow/orange/red as it already is doing.

You would only need 3 conditions then.