Email:      Pass:    Pass?

Free Excel Forum

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?

Similar Excel Video Tutorials

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

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

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?

Hi everyone I am new here and could use some help.

Here is what I am looking for:

I need to be able to get a true/false from a tolerance percent.

Here is an example of what I am trying to do

cell a2 is Nitrogen
cell b2 is (Known gas%) 2.4800%
cell c2 is (unknown gas%) 2.4963%
cell d2 is =b2-c2 and I get the answer no trouble there. what I need is to take the answer in cell d2 and set a plus/minus 2% tolerance in cell f2 and get a true/false comparison.

I hope that made some kind of sence Thanks in advance for any help.


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.

Firstly as this is my first post, Hello to all,

I am looking for some help with creating a formula.

Cell A1 has a measurement from an engineering drawing eg(30725)
Cell A2 has the actual measurement from the job eg(30737)
The formula I would like to create would tell me that the actual measurement (A2) falls within a tolerance of plus or minus 25 from the engineering drawing measurement (A1), and flag this up maybe by turning the cell red or something if the actual measurement is out of tolerance. (if thats possible)

I know you can tell this by just looking at the cells but this with be one of many tolerances for different measurements on an inspection worksheet I am trying to create. i will be comparing alot of actual measuremnents with engineering drawing measurements to specified tolerances. I think I may be able to grasp the idea of writing my own formulas for this if someone can help me with the first one. I am a newbie to excel and have tried and failed myself so any help would be much appreciated.

Thanks in advance.

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.

Hi, i need a conditional formula that will look in Col A, and if it finds a number that begins with 1410, 1512 and if the tolerance level in Col C is above 15%, then shade the Cell in Red.

GICS Status Tolerance 1410876 Outstanding 7% 151254 Outstanding 15% 1410876 Outstanding 56% 1410876 Outstanding 5% 344324 Outstanding 45% 2342 Outstanding 3% 15124 Outstanding 7% 2423 Outstanding 12% 1410876 Outstanding 6% 74643 Outstanding 45% 97453 Outstanding 65%

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?


How do a return a value of 1 from a entry within a given tolerance,i.e. target tolerance of -0.05% to 0.05%?

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.

I was just wondering if anybody has a function to see if a value is between two other values. I am trying to set up a worksheet for my students and want them to enter an answer, but I want to allow their answer if it is within some specified tolerance (a variable input parameter).

Currently I have an if within an if, but I would like to create a function that accepts a tolerance parameter and a value.

For example, if the correct answer is 24.4, I would like to see if their answer is within 0.1% (24.3756 - 24.4244)

What I have now is an if within an if, but I would rather create a function that can be used again, and one that will allow me to change the tolerance (maybe set it in a hidden cell).

Any ideas?


I have 2 sheets in a workbook.
The Form Sheet and a Data Sheet.
The Data Sheet gets formatted and any dimensions that are out of Tolerance in Col "G" is high lighted in Yellow.
On the Form Sheet I have a Macro Button the pulls all the data over, formats the page, etc.

How can I not copy over any data that is out of tolerance?
Not as easy as I thought.!!!