Conditional formatting for plus or minus the SD

[herm0154]

Hello,

I need some help, I can't figure out how to do this right. I have a set of averages that I want to compare (columns c, d...au) to avg 1(column a). I need to highlight those cell averages that are greater than or equal to or less than or equal to the avg1 plus or minus the SD. How can I do a formula or conditional formatting to have those columns that are plus or minus the SD to appear in red (or any color)?

Thanks in advance for your help!

A	B	C	D
Avg 1	SD	Avg 2	Avg 3
2.08	.97	3.5	1.1
3.19	1.24	3.2	2
3.14	1.21	1.5	3.1
2.18	.89	2	2.8

 

[Eric W]

Try:

Book1
	A	B	C	D
1	Avg 1	SD	Avg 2	Avg 3
2	2.08	0.97	3.5	1.1
3	3.19	1.24	3.2	2
4	3.14	1.21	1.5	3.1
5	2.18	0.89	2	2.8
Sheet47

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:D5	Expression	=OR(C2>=$A2+$B2,C2<=$A2-$B2)	text	NO

Of course, adapt the selected range to include your whole data set.

 

[JamesCanale]

So this should highlight anything outside of the SD Range

MrExcelPlayground6.xlsx
	A	B	C	D
1	Avg 1	SD	Avg 2	Avg 3
2	2.08	0.97	3.5	1.1
3	3.19	1.24	3.2	2
4	3.14	1.21	1.5	3.1
5	2.18	0.89	2	2.8
Sheet9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:D5	Expression	=OR(($A2+$B2)<C2,($A2-$B2)>C2)	text	NO

 

[herm0154]

Try:

Book1
	A	B	C	D
1	Avg 1	SD	Avg 2	Avg 3
2	2.08	0.97	3.5	1.1
3	3.19	1.24	3.2	2
4	3.14	1.21	1.5	3.1
5	2.18	0.89	2	2.8
Sheet47

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:D5	Expression	=OR(C2>=$A2+$B2,C2<=$A2-$B2)	text	NO

Of course, adapt the selected range to include your whole data set.

This is great. Thanks.
Follow up question, what if I wanted to highlight cells that were equal to or greather or equal to or less than half the SD?

 

[herm0154]

So this should highlight anything outside of the SD Range

MrExcelPlayground6.xlsx
	A	B	C	D
1	Avg 1	SD	Avg 2	Avg 3
2	2.08	0.97	3.5	1.1
3	3.19	1.24	3.2	2
4	3.14	1.21	1.5	3.1
5	2.18	0.89	2	2.8
Sheet9

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:D5	Expression	=OR(($A2+$B2)<C2,($A2-$B2)>C2)	text	NO

This is great. Thanks.
Follow up question, what if I wanted to highlight cells that were equal to or greather or equal to or less than half the SD?

 

[Eric W]

Just change it to:

=OR(C2>=$A2+$B2/2,C2<=$A2-$B2/2)

I assume you meant cells that differ from Average 1 by half the SD.

 

[herm0154]

Just change it to:

=OR(C2>=$A2+$B2/2,C2<=$A2-$B2/2)

I assume you meant cells that differ from Average 1 by half the SD.

I guess I should elaborate, if I want to make sure I highlight red (for example), the cells that are +/- the SD but then I also want to highlight (yellow) the cells that are within half of the SD (+/-) how to I make sure that they do not overlap? Do you know what I mean?

 

[Eric W]

You'd need 2 rules, one for red, one for yellow:

Book1
	A	B	C	D
1	Avg 1	SD	Avg 2	Avg 3
2	2.08	0.97	3.5	1.1
3	3.19	1.24	3.2	2
4	3.14	1.21	1.5	3.1
5	2.18	0.89	2	2.8
Sheet47

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:D5	Expression	=OR(C2>=$A2+$B2,C2<=$A2-$B2)	text	NO
C2:D5	Expression	=OR(C2>=$A2+$B2/2,C2<$A2-$B2/2)	text	NO

But you need to make sure they are in the right order as shown. If they aren't, then you'll get all yellow, or no yellow. Use the Manage Rules dialog to put them in the right order if you need to.