Go Back

Determine If a Value Is Within a Tolerance in Excel

Figure 1. of Value Within Tolerance in Excel.

In order to determine whether or not a value is within our expected level of tolerance, we are going to utilize the ABS and IF Functions in Excel. This article will walk through the process

Generic Formula

=IF(ABS(actual-expected)<=tolerance,”Yes","No")

The Excel “IF” function calculates whether or not a value logically or mathematically satisfies a set of specified conditions.

How to use the IF and ABS Functions in Excel.

We are going to test whether or not some data values fall within our expected tolerance levels by following 3 simple steps;

  1. Label the ACTUAL VALUE,  EXPECTED VALUE and TOLERANCE columns in our worksheet.

Also, have the results column (WITHIN TOLERANCE) ready as shown in the example illustrated below;

Figure 2. of Value Within Tolerance Table in Excel.

  1. Now, in the event that we want to discover if the expected values (in column B above),  fall within our budget tolerance levels (in column C above), we are going to enter the following IF and ABS operation syntax into cell D2 of our worksheet;

=IF(ABS(A2-B2)<=C1:C2,"YES","NO")

Figure 3. of the IF and ABS Operation Syntax in Excel.

  1. Copy the IF and ABS operation syntax into into the cells down the entire column D to achieve the desired results for each value.

In the example, our Expected data value is subtracted from the Actual or Measured data value. Our resulting value may be negative or positive, depending on our Actual data value. Thus, we can utilize the Excel ABS Function in order to convert all of our results into positive values.

The formula then proceeds to compare this positive result to our specified tolerance level, by way of a logical test within the IF formula syntax. In the event that the difference is less than or equal to our specified tolerance level, IF is returned as “YES”. In the event that the difference is greater than or equal to our specified tolerance level, IF is returned as “NO”.

Figure 4. of Final Result.

Instant Connection to an Expert through our Excelchat Service

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

G is high tolerance H is low tolerance, I is the actual #, so when the actual # is input can row J highlight in green as a pass and K highlight in red as a fail? If row i is not within the G and H #'s?
Solved by X. A. in 13 mins
Hi, I need to build a formula that is dependent on 2 variables and is shown within Cell 'E35'. If the total of 'D30' equals '20' but there is any value within 'F15', show the value of 'F15' within 'E25' BUT discard the transposed value within 'E35'....I hope that makes sense? Thanks Simon
Solved by I. W. in 15 mins
I need an IF formula for if the value of cell G9 is greater than the value of H9 then enter the numerical value (already within G9) of G9 into this cell. if the value of G9 is less than the value of H9 then enter the numerical value (already within H9) of H9 into this cell.
Solved by O. A. in 19 mins

Leave a Comment

avatar