Apr 06 2020 07:44 AM - edited Apr 06 2020 08:43 AM
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 |
Apr 06 2020 07:51 AM
@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)
Apr 06 2020 08:41 AM
@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.
Apr 06 2020 08:53 AM
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)
Apr 07 2020 04:43 AM
@Savia Thanks for the assistance Savia, this works great!
Feb 22 2021 11:41 AM
@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.
Feb 22 2021 12:03 PM
@karina8581 This is what the final equation looks like in reference to cells in the original post. Hope it helps.
Feb 22 2021 12:55 PM
@MicLima Wow! That worked!! Thank you.
Apr 06 2020 08:53 AM
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)