Forum Discussion

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

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)

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)
- MicLima
  Copper Contributor
  Apr 07, 2020
  Savia Thanks for the assistance Savia, this works great!
  - karina8581
    Copper Contributor
    Feb 22, 2021
    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.