SOLVED

New Contributor

# 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 NominalValue Tolerance(+) Tolerance(-) C11 C22 C33 D14 D25 D36 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
7 Replies

# Re: Conditional formatting using tolerances

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

# Re: Conditional formatting using tolerances

@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

# Re: Conditional formatting using tolerances

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

# Re: Conditional formatting using tolerances

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

# Re: Conditional formatting using tolerances

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

# Re: Conditional formatting using tolerances

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

# Re: Conditional formatting using tolerances

@MicLima Wow! That worked!! Thank you.