Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

Formulas With Tolerance

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am creating a Test Data Record sheet, I can't seem to find a formula that includes tolerance. For example, A2 value should be whatever A1 is, plus/minus .010". So if a number outside that tolerance is entered the cell will display red and green if is within. Test sheet is almost done but that formula. Can someone help with this conditional formula?

View Answers     

Similar Excel Tutorials

Show All Formulas in a Worksheet in Excel
Display all formulas instead of their output values. This allows you to quickly troubleshoot issues in a worksheet ...
Display all Formulas at Once in Excel
How to view all of the formulas at once in Excel so that you can troubleshoot the spreadsheet quickly and easily. S ...
Stop Formula Calculation in Excel - Increase Worksheet Performance
I'll show you how to stop formulas and functions from performing their calculations and updating in Excel. This ma ...
Highlight Cells which Contain Formulas
This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highl ...

Helpful Excel Macros

Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Name Worksheets Based on Cell Contents
- This macro allows you to have your worksheets named by whatever is in a particular cell within a worksheet. This means
Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to

Similar Topics

Trying to identify values that are over 30min late or over 30min early

AC1 = 00:30
AC2 = -00:30

Column J Column AC
Variance Tolerance

00:01 In Tolerance
-00:01 Outside Tolerance
00:00 In Tolerance
00:31 Outside Tolerance
-00:31 Outside Tolerance
-00:04 Outside Tolerance
00:00 In Tolerance
00:48 Outside Tolerance
01:05 Outside Tolerance
-00:48 Outside Tolerance
-01:05 Outside Tolerance

=IF(AND($J5$AC$2),"In Tolerance","Outside Tolerance")

Seems to work for over 30min late, but not for over 30min early. All negative values return 'Outside Tolerance'

Any thoughts

Many Thanks

Hello all!

I am in need of some assitance. I am making a dimensional inspection report in which I need to change a cell color to green or red. Green meaning it is within tolerance, red meaning it is out of tolerance. I also need to be able to define the tolerance. so it might go like this.......

column A would be the nominial number for example 2.000
column B would be the tolerance for example plus/minus .005
column C would be the actual number for example 1.992
In this instance Column C would turn red because it is beyond the .005 tolerance. If colunm C actual number was 1.999, it would turn green. I know this is possible just don't really know how to do. Any help is greatly appericated. Not sure if this matters but I need it to be about 25 rows.

Thanks again,


Hello all!

I am in need of some assistance. I am making a dimensional inspection report in which I need to change a cell color to green or red. Green meaning it is within tolerance, red meaning it is out of tolerance. I also need to be able to define the tolerance. So it might go like this.......

Column A would be the nominal number for example 2.000
Column B would be the tolerance for example plus/minus .005
Column C would be the actual number for example 1.992
In this instance Column C would turn red because it is beyond the .005 tolerance. If column C actual number was 1.999, it would turn green. I know this is possible just don't really know how to do. Any help is greatly appreciated. Not sure if this matters but I need it to be about 25 rows.

Thanks again,


I'm working on updating a spreadsheet that calculates whether or not a project is within tolerance or above or below a tolerance level based on the value of the project and then a corresponding formula.

The formulas as written (see tolerances tab) work but the data is not static and the project values can change and hence the tolerance formula needs to update.

I guess I can try to do a nested IF formula but I'm really bad with the paranthesees and seem like I can never get them right.

Any suggestions? (formulas and data attached hopefully )

I have (2) sheets in my workbook.
One is a P/N (123456) the other is labeled "REPORT.

The REPORT Sheet contains the Inspection data.
I run a macro to test for any dimensions that are out of tolerance and I highlighted them in "yellow". They fall in Col "C" starting at C14.

C13 is labeled as "ACTUAL".
In D13 is the Nominal dimension (or what the recorded dimension should be).
E13 is the Upper Tolerance and F13 is the lower Tolerance.

B13 is labeled as "Characteristic".
B14 = 1
B15 = 2
B16 = 3, etc thru and including 23.

So for example.
C17 = 9.5009
D17 = 9.5000
E17 = .0003
F17 = .0000
So the Dimension taken should read 9.5000 with a tolerance of +0.0003 - 0.0000.
So this dimension is out ot tolerance.

Right now I have 4 that are out of tolerance and I don't want them pulled over.

On the 123456 sheet is the customers report.
It lists all the required data which I will pull off the REPORT sheet.
There are only 17 required dimensions to be listed.
I use the following fx to pull the data:

What I don't want to get pulled is the data that is out of tolerance.
How can I do that? Is there a way to add something to the IF formula that tests for this prior to running the VLOOKUP ? I'm still playing the formula to achieve my result.

Or does someone have a suggestion.


I need to compare two data entries in two different worksheets. The numbers are fairly large and may be entered by different people using the workbook at different times.

So, lets say in sheet 1, A1, The data is 105560
and in sheet 2, A1 The data is 105562

I want to write a formula like, =if(sheet1!A1=sheet2!A1,"ok","error")

However, a tolerance of 2 is acceptable and so I don't really want to show an error in this case.

Is there a way of writing the formula that will allow for a tolerance of my choice when comparing two numbers. It should not matter if which way around the tolerance is (if so it would be easy).

I did think of using the Round function but I'm not dealing with decimal places.

If anyonre can help that would be smashing

I use this formula to calculate if a move is out of tolerance =IF(ABS(B1)>=1,"Y","N") This will work on most of the stuff we load. I would like to modify it to if b1 = "Crude" then use =IF(ABS(B1)>=.5,"Y","N"), or if B1 = "Resid" then use IF(ABS(B1)>=2.0,"Y","N"). Is it possible to include the 3 tolerance bands in one formula?

iam looking for formula to display the condtion result .

i have spec in col B and tolerance in col C and col D ( +ve & -ve tolerance respectively) from col E to I ,observed reading data & col j for results.

i want to check the value of col E to I for B C D value n if entire 5 values are within spec then display "0" in subsequesnt col J else display "1"
OBSERVED RESULT S.L.NO SPEC `+TOLERANCE `- TOLERANCE 1 2 3 4 5 1 25 0.2 0.2 26 25 25.2 25.4 25.2 2 10 0.2 0.2 3 5 0.2 0.2 4 5 0.2 0.2 5 51 0.2 0.2

hi, hope someone can help me please?
I have 3 cells with values Cell AJ35 is the calculated value, Cell AJ34 is the upper tolerance and Cell AJ36 is the lower tolerance. If AJ35 value is above the upper tolerance (AJ34) I want a message to say "light output too high, reduce resistance on decade box" and a retry the test or cancel buttons to end the test, if retry I want the code to loop until the value is between the upper and lower tolerance.
if the value is below the lower tolerance I want a message to say "light output too low, increase resistance on decade box" with retry and cancel buttons to do the same. once the AJ35 cell is between the upper and lower limit I would like it to continue running the rest of my code.
this is what I have so far: -

Please Login or Register  to view this content.

any help would be appreciated, thanks in advance


I am attempting to use conditional formating to provide obvious accept/reject messages for dimensional inspections being conducted. If the inspection data is within the measurement tolerance, the conditional format will display the value in green and if the value is out of the measurement tolerance, the conditional format will display the value in red.

I have a 3 decimal place measurement requirement. I am using a formula that averages 4 measurements that are taken to determine the acceptablity of the dimension. I then round the average to 3 decimal places to display the value.

Conditional formating however apparently uses all existing decimal places when it evaluates the value in that cell. As a result, a number displayed as 3 decimal places and when averaged is within the limit, displays as value in red or "rejected".

Example: Upper Limit: 3.420
Average of 4 Dimensions: 3.4201
Rounded to 3 decimal places: 3.420 (within tolerance)
Conditional format displays the 3.420 in red indicating it is out of limit. Obviously it is not using the rounded value.

I believe this is caused by the lack of rounding by the conditional formating. In other words, it appears conditional formating uses all available decimal places to determine the value of the averaged number and sees the value as out of the tolerance. In the cell containing the value, I have tried using the Round and Fixed formats in the formulas with no sucess.

Any ideas how to control conditional formating to use only 3 decimal places?

I have a huge database with about 4000 rows that contain angle measurement data that are in 10 columns. For each row I need to find/display which of the of the values are within plus or minus a value specified in a cell that I can change. Right now I am not sure of what form the solution (output) will be easiest to use/view. Because of this I would like to see at least two different solution. One would be to use conditional formatting and format each of the angle values that is within the tolerance value. There are 10 angle measurements on each row. If the value in angle 1 equals 2 other values on that row within the tolerance, then I need the conditional format color to be the same. And if two others in that same row match each other (but not the same as the first 3 matches), then I would need them to be conditional formatted in another color. In my example at L5 I show the tolerance value that I can change to view different results from difference tolerances. It might be possible that later on I would need a difference tolerance for each angle column. I will not know this until I view the results.
I am not sure how the conditional format will work, so to the right of the database I have added a result area. It is possible that it will be much easier for me to find the answer I seek by counting the number of cells that are within the tolerance value. So, this area could have the count for the matching values for each row so that I could evaluate the top row against all of the other row results to see how they relate, by using the column filter.
I hope that I have explained this completely and that the example I supplied is OK. I am new to the forum and am NOT a real Excel heavyweight. I run Excel 2010 on a windows 7 desktop. Thank you for any help that you can provide. donbw.


I know there must be an example of what I am looking for but I have been unsuccessful in locating an example that works for me.

I have 2 columns of data for Deviations and Out of Tolerance. Currently I have the Out of Tolerance column formatted so that it displays the text in red if a measurement is out of tolerance. I would like the cell next to the o.o.t. cell (showing the deviation) to also display in red if the o.o.t. cell is red.

I tried conditional formatting by o.o.t. cell >0 and <0 but that didn't work.

Please Login or Register  to view this content.

In this example, b3, d2, d3, and d4 are all o.o.t. and as such are displayed in red in my spreadsheet. I now need to make a3, c2, c3 and c4 display in red as well.

Any help would be most appreciated!


Hello Members, I have been having troubles creating a formula to do what I need. with dimensions and tolerances.

I need the nominal of a number which is calculated from the maximum tolerance and the minimum tolerance then the equal differance in the tolerances.

If I have in cell A1 .5000 and a tolerance in cell B1 +.008 then in cell C1 -.004
The nominal numbers would be in cell A2 .502 in cell B2 +.0006 in cell C2 -.0006

Regards, Tom

I am trying to create a spreadsheet for Clothing QC Inspectors to use when checking measurements on garments. Each garment has various points of measurements with a specification for each size and a +/- tolerance for each point of measuring.

For instance I have Garment A with a small size spec of 24 inches and the tolerance is +/- 1/2".

What i would like to do is allow the inspector to just enter the measurement and have excel do the math for them which would take the spec+/-tolerance-measurement for the result and would look something like this:

POM Tolerance +/- Size/Spec Sample 1 +/- TOL Sample 2 +/- TOL Sample 3 +/- TOL Sample 4 +/- TOL

Waist Relaxed 1/2 26 26 0 23 -1 1/2 28 1 1/2 26 3/4 1/4

Any ideas for a formula to +/- the tolerance? I'm totally lost...

(I apologize for the wacky format on the above table, i do not have admin rights to download the htmlmaker)

Hi everyone,
I am writing a macro that will take a column of numbers (integers) and will randomly +/- a random value between 0 and 300. I managed to get past this part.
The catch is that the sum of the original and new column must be within a certain tolerance.
I used the random formula and asked the macro to Do while --- Calculate until the abs difference is greater then tolerance.

The problem is past a certain tolerance it won't work, probably because itr randomizes all the nu,bers every iteration. Any better ideas to accomplish this feat?

I currently have my excel books to have dimensions in conditional format to where dimensions in tolerance are green and dimensions out of tolerance are red. I am also looking to add a pop up message when dimensions out of tolerance can have a pop up message and turn red. Is there any code out there that would allow me to link the pop up messages that are linked to my set conditional format?

In need of H E L P, Thank you to everyone in advance.

First off, I did search this and came up with nothing so I hope this hasn't been posted b4.

I'm calibrating gauges and making a log of them, and what I need to do is when a gauge is out of tolerance I need it to be highlighted

example say I have an applied pressure in A1, the indicated pressure in B1 and the Difference between the two in C1. I already have the simple formula =B1-A1 in C1... but I need it to automatically highlight in red if the answer is out of tolerance, notated lets say in D1 (+ or - ). so if A was 1 B was 2 making C 1 and the tolerance was .5 i need that answer to highlight.

I have a workbook that tracks date of completion of classes. The dates section are conditional formatted to change colors to red if they are out of tolerance, orange if 30 days away from expiring, yellow if 60 days away and return to green if they are in tolerance. I am trying to add a auto count section that shows how many cells are green, red, yellow and red and have a percentage of below that reflecting the total against the total number. Any help would be greatly appreciated.

Hi, Does anyone know if it is possible on conditional formatting to build in a tolerance figure, I am building a validation checking sheet and I want to allow certain amount above and below the correct figure before it flags to indicate I have an issue.

I'll try to explain this the best I can. I've got multiple pages of spread sheets with various standard washer sizes. They all have the upper tolerance and lower tolerance for the dimensions of the inner diameter, outter diameter and the thickness. Is there anyway that I can input the dimensions for a given washer to see if it falls into one of these standards without manually looking through the ten plus pages?


Hi there,

This is my first post but I often seek help for excel queries so Im sure it won't be my last.

I am trying to perform the following:

If $D$5 = "SET" AND $E$5 = between 39.0 and 41.0 then display value entered and make $E$5 have Green background


if $D$5 = "CHECK" AND $E$5 = between 37.0 and 43.0 then display value and make $E$5 Green background

If the value entered is outside of these tolerances OR "SET / CHECK" is not entered in $D$5 then make $E$5 have RED background

For background info' my staff have to perform quality checks on the scales of a measuring device.

If they enter "SET" then the tolerance of that scale is between 39.0 and 41.0. If it passes then the cell should turn Green. If it fails it should turn Red.

If they enter "CHECK" then the tolerance of that scale is between 37.0 and 43.0. If it passes then that cell should turn green. If it fails it should turn Red.

If they fail to write either "SET" or "Check" then it should either tell them, or for simplicity just turn red.

I have tried contional formatting but couldn't get this to work with the "SET/CHECK" input.

I also tried the following (this doesn't include changing cell colour) and it also didn't work:

I have reached the limits of my Excel knowledge.
Thanks in advance for your assistance.


Hi Everyone,

I need help, I need a bar chart with three bars, for each month, for six month in a row.

Each of these bars I also want two data points, one reflecting the target and one reflecting the the tolerance, with a high low line connecting the target and tolerance.

Example: Cab Bar with a diamond for the target and a dash for the tolerance with a line between them. Is this possible?

Attachment:Sample Data, I am not sure if this is the configuration that the data needs to be in???

Totally Lostinformulas

I'm currently working on building a spreadsheet that I use while calibrating equipment so I can check if a value is within tolerance. I have applied conditional formatting to B4 so that if the value entered is between 17.6 and 26.4 it turns green. What I need to do is also turn the adjacent cell, C4, green at the same time without changing the text in C4. I have tried using conditional formatting in C4 using the formula

I have a report that is generated from a manufacturing process that looks like the example below. the report is 40 pages long when all the data is printed. i am looking for a way to only print this range if a dimension is "out of tolerance". if the dimension is within tolerance, there is always the "garbage" text of plus and minus. if every row is "within tolerance" in the range the cells in the OutTol column would all contain the "garbage" text but it will not always be identical. so, in summary, actual OutTol values = print and all "garbage" = not printed. Any takers? Thanks in advance.

Nom Act Dev LoTol UpTol OutTol
Y -0.956 -0.948 0.008 -0.003 0.003 0.005
Z -1.413 -1.413 0.000 -0.003 0.003 ---*|++++
DIA 0.422 0.424 0.002 -0.003 0.006 ----|+*++
POS 0.016 0.011 0.005

Hi, I searched on the forums and found some answers to tolerance and duplicate related questions, but this one is a little different. Below is a short segment of my excel spreadsheet:

1020.00242 -89.84
1020 -88.11
1021 -85.3
1021.494 -80.41
1021.49 -86.98
1030.04 -89.4
1030.042 -88.26
1030.94 -79.98
1030.933 -81.5
1030.961 -85.87
1040.0418 -88.77
1040.391 -87.3
1040.291 -82.94
1040.016 -84.12
1049.82 -84.7

What I need to do is write a macro that will find duplicates in Column A, within a changeable tolerance, say 0.1 (10%).

After finding all duplicates within a tolerance in A, I need to make another "Master" worksheet with the Duplicates from A, and their counterpart in B. So if A1 and A4 where within 10% of each other, the "Master" worksheet would contain:

A1 B1
A4 B4

using the values, giving:

1020.00242 -89.84
1021.494 -80.41

I tried using SUMPRODUCT and some other functions but just can't seem to put my finger on this one. I'm sure it's not hard and am overlooking something. Any help would be greatly appreciated. Thanks.