Hi, How would I write the formula to format the below cells if they are out of tolerance? Thanks in advance! EFGHIJKLM13NominalValueTolerance(+)Tolerance(-)C11C22C33D14D25D361460.506.0636.1196.0966.0956.0726.0731580-0.018.0048.0028.0058.0048.0028.002

MicLima Yes, this would work with the rule I wrote as-is. The only difference is that I assumed that your negative tolerances would be expressed as positive numbers (your original example had a 0 negative tolerance so I couldn't tell either way). Fixing for the correct sign: =OR(D3-$A2>$B2, D3-$A2<$C3)

Forum Discussion

MicLima

Copper Contributor

Apr 06, 2020

Solved

Conditional formatting using tolerances

Hi,

How would I write the formula to format the below cells if they are out of tolerance? Thanks in advance!

	E	F	G	H	I	J	K	L	M
13	Nominal Value	Tolerance (+)	Tolerance (-)	C1 1	C2 2	C3 3	D1 4	D2 5	D3 6
14	6	0.5	0	6.063	6.119	6.096	6.095	6.072	6.073
15	8	0	-0.01	8.004	8.002	8.005	8.004	8.002	8.002

excel

Formulas and Functions

Savia
Apr 06, 2020
MicLima Yes, this would work with the rule I wrote as-is. The only difference is that I assumed that your negative tolerances would be expressed as positive numbers (your original example had a 0 negative tolerance so I couldn't tell either way). Fixing for the correct sign:

=OR(D3-$A2>$B2, D3-$A2<$C3)

7 Replies

Savia
Iron Contributor
Apr 06, 2020
MicLima Something like this (written for cell D3; use dollar signs as I have and this rule can be applied to all cells):

=OR(D3-$A2>$B2, $A2-D3>$C3)
- MicLima
  Copper Contributor
  Apr 06, 2020
  Savia Thanks for the help! I updated the original post; is there a way to do this for multiple sources and tolerances for all cells? I tried playing around with the absolutes, but couldn't get it to work right.
  - Savia
    Iron Contributor
    Apr 06, 2020
    MicLima Yes, this would work with the rule I wrote as-is. The only difference is that I assumed that your negative tolerances would be expressed as positive numbers (your original example had a 0 negative tolerance so I couldn't tell either way). Fixing for the correct sign:
    
    =OR(D3-$A2>$B2, D3-$A2<$C3)