SOLVED

Conditional formatting using tolerances

%3CLINGO-SUB%20id%3D%22lingo-sub-1285006%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20using%20tolerances%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1285006%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F611147%22%20target%3D%22_blank%22%3E%40MicLima%3C%2FA%3E%26nbsp%3BSomething%20like%20this%20(written%20for%20cell%20D3%3B%20use%20dollar%20signs%20as%20I%20have%20and%20this%20rule%20can%20be%20applied%20to%20all%20cells)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DOR(D3-%24A2%26gt%3B%24B2%2C%20%24A2-D3%26gt%3B%24C3)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1285163%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20using%20tolerances%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1285163%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20help!%20I%20updated%20the%20original%20post%3B%20is%20there%20a%20way%20to%20do%20this%20for%20multiple%20sources%20and%20tolerances%20for%20all%20cells%3F%20I%20tried%20playing%20around%20with%20the%20absolutes%2C%20but%20couldn't%20get%20it%20to%20work%20right.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1284987%22%20slang%3D%22en-US%22%3EConditional%20formatting%20using%20tolerances%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1284987%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20would%20I%20write%20the%20formula%20to%20format%20the%20below%20cells%20if%20they%20are%20out%20of%20tolerance%3F%26nbsp%3B%20Thanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22566px%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2272px%22%3EE%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3EF%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3EG%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3EH%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3EI%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3EJ%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3EK%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3EL%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3EM%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E13%3C%2FTD%3E%3CTD%20width%3D%2272px%22%3ENominal%3CBR%20%2F%3EValue%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3ETolerance%3CBR%20%2F%3E(%2B)%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3ETolerance%3CBR%20%2F%3E(-)%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3EC1%3CBR%20%2F%3E1%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3EC2%3CBR%20%2F%3E2%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3EC3%3CBR%20%2F%3E3%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3ED1%3CBR%20%2F%3E4%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3ED2%3CBR%20%2F%3E5%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3ED3%3CBR%20%2F%3E6%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E14%3C%2FTD%3E%3CTD%20width%3D%2272px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3E0.5%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3E%3CFONT%20color%3D%22%23000000%22%3E6.063%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3E%3CFONT%20color%3D%22%23000000%22%3E6.119%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3E%3CFONT%20color%3D%22%23000000%22%3E6.096%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3E%3CFONT%20color%3D%22%23000000%22%3E6.095%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3E%3CFONT%20color%3D%22%23000000%22%3E6.072%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3E%3CFONT%20color%3D%22%23000000%22%3E6.073%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2240px%22%3E15%3C%2FTD%3E%3CTD%20width%3D%2272px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2280px%22%3E-0.01%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3E8.004%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3E8.002%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3E8.005%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3E8.004%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3E8.002%3C%2FTD%3E%3CTD%20width%3D%2249px%22%3E8.002%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1284987%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1285196%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20using%20tolerances%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1285196%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F611147%22%20target%3D%22_blank%22%3E%40MicLima%3C%2FA%3E%26nbsp%3BYes%2C%20this%20would%20work%20with%20the%20rule%20I%20wrote%20as-is.%26nbsp%3B%20The%20only%20difference%20is%20that%20I%20assumed%20that%20your%20negative%20tolerances%20would%20be%20expressed%20as%20positive%20numbers%20(your%20original%20example%20had%20a%200%20negative%20tolerance%20so%20I%20couldn't%20tell%20either%20way).%26nbsp%3B%20Fixing%20for%20the%20correct%20sign%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DOR(D3-%24A2%26gt%3B%24B2%2C%20D3-%24A2%26lt%3B%24C3)%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288060%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20using%20tolerances%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288060%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F544433%22%20target%3D%22_blank%22%3E%40Savia%3C%2FA%3E%26nbsp%3BThanks%20for%20the%20assistance%20Savia%2C%20this%20works%20great!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
4 Replies
Highlighted

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

Highlighted

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

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

Highlighted

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