Tolerance to show out in color

[JE79]

I need a spreadsheet that will show Mean/Target, +/- tolerance but then have the results show Red if out of tolerance. Either text red or cell red. For more than 1 result. Do not need the result "OK" or "NG". Just trying some formulas on that column.

 

[etaf]

would you mind explaning the image

with some examples of expected results and why

mean/target = 2.8
Tolerance is +/- 0.1
and result is 2.9
so NOT coloured

BUT

mean/target = 0.5
Tolerance is +/- 0.006
and result is 0.508

so thats no good as the tolerence is 0.006 - so
0.494 or 0.506 is the range

so i have used ABS() and subtracted the result from the target - so we get + regardless
and then compared to the tolerance

is that what you are after

Book10
	A	B	C	D	E	F	G	H	I	J
1
2			2.8	0.1	ok	2.9		0.100		FALSE
3			0.5	0.006	ng	0.508		0.008		TRUE
4			2.8	0.1	ok	2.8		0.000		FALSE
5			153.1	0.15	ng	152		1.100		TRUE
6
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=ABS(F2-C2)
J2:J5	J2	=H2>D2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F5	Expression	=ABS($F2-$C2)>$D2	text	NO

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
F2:F100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=ABS($F2-$C2)>$D2

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[JE79]

Sorry, maybe this is a better example of what I need:

 

[DanteAmor]

Try

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1		Char	Mean	+/-	Met	1	2	3	4	5
2		OD	2.8	0.1	ok	2.9	3	2.7	3.1	2.8
3		ID	0.5	0.006	ng	0.506	0.508	0.505	0.502	0.507
4		Lenght	2.8	0.1	ok	2.8	3	2.9	2.8	2.7
5		Lenght	153.1	0.15	ng	154	153.2	153.3	153.1	153.3
Hoja4

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

----- --
NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[JE79]

Not getting out of tolerance results to show red.

 

[etaf]

=ROUND(ABS(F2-$C2),10)>$D2
i have used round as i was getting a floating point error - which excel does do occasionally

Book10
	A	B	C	D	E	F	G	H	I	J
1
2			2.800	0.100		2.900	3.000	2.700	3.100	2.800
3			0.500	0.006		0.506	0.508	0.505	0.502	0.507
4			2.800	0.100		2.800	3.000	2.900	2.800	2.700
5			153.100	0.150		154.000	153.200	153.300	153.100	153.250
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:J5	Expression	=ROUND(ABS(F2-$C2),10)>$D2	text	YES

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
F2:J100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=ROUND(ABS(F2-$C2),10)>$D2

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

 

[JE79]

EXACTLY what I needed! Thanks!!!

 

[etaf]

you are welcome - not sure who your reply is too, not that it matters

 

[JE79]

Any and all that helped.

 

[etaf]