If between a and b but not between x and y- Standard deviation

[Nick_b]

I am trying to highlight cells which are between two numbers but not between two other numbers in order to highlight data values used to create a histogram using standard deviation. I understand that I would use conditional formatting but I'm not sure how to set up the equations. Here is briefly what I am trying to achieve and my data:

mean + 1 sd: .7685
mean - 1 sd: .7313
mean + 2 sd: .7871
mean - 2 sd: .7126
mean + 3 sd: .8058
mean - 3 sd: .6940

- highlight red if it is not between .6940 and .7685 I have this formula as '=OR(B5<.6940,B5>.7685)'
- highlight light blue if it is between .7313 and .7685
- highlight medium blue if it is between .7126 and .7871 but not between .7313 and .7685 (or another way to look at it would be to highlight light blue it was between .7126 and .731 or between .7685 and .7871)
- highlight dark blue if it is between .6940 and .7685 but not between .7126 and .7871.

I am an engineering teacher and my students will be using the data to find 27 hardwood cubes which will be glued into 6 pieces to make a 3d puzzle cube. This will incorporate statistics and quality control

 

[FDibbins]

Conditional Formatting (CF) works only on TRUE/FALSE, so you need to construct your rules to return 1 of those. So that 1str formula should work...
=OR(B5<.6940,B5>.7685)

To actually apply the CF
1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. Enter =OR(B5<.6940,B5>.7685) Format Fill RED

Repeat 3 and 4 with each new rule.

I often construct the rule IN the worksheet, so that I can make sure I an getting the return I need. Try this and let me know how you make out

 

[Nick_b]

Thank you. I understand the procedure to apply the conditional formatting and have for the example formula I gave. It works great! The other formulas are where I am having trouble. I want to highlight the values between two numbers but not include a subset of those. For example highlight all the values within +/- 2 standard deviations but not include the values +/- 1 standard deviation. I understand the basic formula. It is not including the values in the middle I need help with. I have linked a url to download the file if that would be a help. https://drive.google.com/file/d/0BxlpOUh650GLNFQxRmZHU1Z3OE0/view Thank you again

Conditional Formatting (CF) works only on TRUE/FALSE, so you need to construct your rules to return 1 of those. So that 1str formula should work...
=OR(B5<.6940,B5>.7685)

To actually apply the CF
1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. Enter =OR(B5<.6940,B5>.7685) Format Fill RED

Repeat 3 and 4 with each new rule.

I often construct the rule IN the worksheet, so that I can make sure I an getting the return I need. Try this and let me know how you make out

 

[jtakw]

Hi,

You can use the following formulas for Conditional Formatting your cell(s), I assume when you say BETWEEN and NOT BETWEEN means does not include the referenced parameters, e.g. <, >, NOT <=, >=

Excel 2010
	A	B	C	D	E
2	mean + 1 sd: .7685	0.7685		FALSE	Format Fill Red
3	mean - 1 sd: .7313	0.7313		FALSE	Format Fill Light Blue
4	mean + 2 sd: .7871	0.7871		FALSE	Format Fill Medium Blue
5	mean - 2 sd: .7126	0.7126		FALSE	Format Fill Dark Blue
6	mean + 3 sd: .8058	0.8058
7	mean - 3 sd: .6940	0.6940
Sheet1

Cell Formulas
Range	Formula
D2	=OR(B5<0.694,B5>0.7685)
D3	=AND(B5>0.7313,B5<0.7685)
D4	=OR(AND(B5>0.7126,B5<0.7313),AND(B5>0.7685,B5<0.7871))
D5	=AND(B5>0.694,B5<0.7126)

 

[Nick_b]

Thank you, Thank you, Thank you! This is exactly what I needed! I did change the formulas to include the equal where necessary and changed the last formula to the same format as the fourth but it works flawlessly. I am amazed at how fast I recieved replies as well. Thank you again.

Hi,

You can use the following formulas for Conditional Formatting your cell(s), I assume when you say BETWEEN and NOT BETWEEN means does not include the referenced parameters, e.g. <, >, NOT <=, >=

Excel 2010

	A	B	C	D	E
2	mean + 1 sd: .7685	0.7685		FALSE	Format Fill Red
3	mean - 1 sd: .7313	0.7313		FALSE	Format Fill Light Blue
4	mean + 2 sd: .7871	0.7871		FALSE	Format Fill Medium Blue
5	mean - 2 sd: .7126	0.7126		FALSE	Format Fill Dark Blue
6	mean + 3 sd: .8058	0.8058
7	mean - 3 sd: .6940	0.6940

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D2 =OR(B5<0.694,B5>0.7685) D3 =AND(B5>0.7313,B5<0.7685) D4 =OR(AND(B5>0.7126,B5<0.7313),AND(B5>0.7685,B5<0.7871)) D5 =AND(B5>0.694,B5<0.7126) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[jtakw]

Thank you, Thank you, Thank you! This is exactly what I needed! I did change the formulas to include the equal where necessary and changed the last formula to the same format as the fourth but it works flawlessly. I am amazed at how fast I recieved replies as well. Thank you again.

You're welcome, glad it worked for you, welcome to the forum.