SOLVED

Conditional formatting using tolerances

New Contributor

Hi,

 

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

 

 EFGHIJKLM
13Nominal
Value
Tolerance
(+)
Tolerance
(-)
C1
1
C2
2
C3
3
D1
4
D2
5
D3
6
1460.506.0636.1196.0966.0956.0726.073
1580-0.018.0048.0028.0058.0048.0028.002
7 Replies

@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)

@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.

best response confirmed by MicLima (New Contributor)
Solution

@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)

@Savia Thanks for the assistance Savia, this works great!

@MicLima 
Can you provide the table image that references the final equation you are using for the conditional formatting? 
For the equation, I dont see the columns you referenced in the equation. I am trying to copy off of yours but not sure which columns the equation is referring to. Thank you. 

@karina8581 This is what the final equation looks like in reference to cells in the original post.  Hope it helps.

 

MicLima_0-1614024179049.png

 

@MicLima Wow! That worked!! Thank you.