Excel formula: Value is within tolerance

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;

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.

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.

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:

Hi, I would like to return a specific TEXT in a cell if another cell is within certain range: e.g. to return the text ''CRITICAL'' if the cell value >=300; return the text ''MAJOR'' if the cell is within the range 65-299; return the text ''OTHER'' if the cell value <65 Thanks

An Excelchat Expert solved this problem in 15 mins!

I want to find a value within a worksheet.

An Excelchat Expert solved this problem in 11 mins!

I have a spread sheet for tolerance. I can either enter the percentage of the dimension, or the +/- value (Cell C5, or Cell D6 respectively). I would like either cell to clear automatically when entering data, so both aren't filled inadvertently. So.... Either cells C5 or D6 can have an entry but not at the same time. If C5 is full and I enter a new value in D6; C5 will clear automatically, and the other way around as well.

An Excelchat Expert solved this problem in 27 mins!