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%3CLINGO-SUB%20id%3D%22lingo-sub-2156745%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20using%20tolerances%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2156745%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%3B%3CBR%20%2F%3ECan%20you%20provide%20the%20table%20image%20that%20references%20the%20final%20equation%20you%20are%20using%20for%20the%20conditional%20formatting%3F%26nbsp%3B%3CBR%20%2F%3EFor%20the%20equation%2C%20I%20dont%20see%20the%20columns%20you%20referenced%20in%20the%20equation.%20I%20am%20trying%20to%20copy%20off%20of%20yours%20but%20not%20sure%20which%20columns%20the%20equation%20is%20referring%20to.%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2156836%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20using%20tolerances%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2156836%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F975118%22%20target%3D%22_blank%22%3E%40karina8581%3C%2FA%3E%26nbsp%3BThis%20is%20what%20the%20final%20equation%20looks%20like%20in%20reference%20to%20cells%20in%20the%20original%20post.%26nbsp%3B%20Hope%20it%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MicLima_0-1614024179049.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256784iB637CDF80130DC6E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22MicLima_0-1614024179049.png%22%20alt%3D%22MicLima_0-1614024179049.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2157003%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20using%20tolerances%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2157003%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%3BWow!%20That%20worked!!%20Thank%20you.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.