Determine If a Value Is Within a Tolerance in Excel

To determine whether a value conforms to the accepted or tolerance levels and report an “ACCEPT” or “REJECT” or a “PASS or “FAIL,” the basic “IF” function can be used. How easy? The Excel “IF” function is fairly simple.

The IF function to determine if a value is within a tolerance

The “IF” function can help you to determine if a set of conditions is True or False, Pass or Fail, and so on. The “IF” function is a good tool for the following:

  • The “IF” function determines whether an expression mathematically or logically satisfies a set of conditions.
  • The “IF” function helps you to know if a set of values is within tolerance levels.

Let us use our Excel sheet for a firmer understanding of how the “IF” function works. We will use two examples.

EXAMPLE 1

 

How the formula works

Inside the IF function, assuming we click on F12 after satisfying other conditions, the result shows a PASS. How did we arrive at this?

STEP 1

  • Type the names and the scores on the spreadsheet. Also, have the result section ready as shown above.

STEP 2

  • Now, assuming we want to determine if the score of Matty is a PASS or FAIL, what do we do?
  • Place your cursor on F15. Click on fx as shown in figure 2 below. You should have this.

STEP 3

  • Click on OK on the popup in figure 2.
  • You will see the figure below.

  • Take your cursor and place it between the brackets on the fx section. That is = IF(PLACE THE CURSOR HERE)

STEP 4

  • Remember that Matty’s score is in CELL E15. So, type E15. We want to know if Matty’s score is greater than or equal to 500, so use the signs and place a comma. Insert a space. Type an inverted comma and then, PASS. Close the inverted comma. Insert a space and do the same for FAIL. When you are done, it should be like Figure 4 below.

  • Click on OK. Did you get a PASS as the result? YES! That is correct.

In a nutshell, this is the basic function =IF(E15>500, “PASS“, “FAIL”)

EXAMPLE 2

In this example, we will use the “IF” function in combination with the “ABS” function. The “ABS” function simply means an Absolute Value Function.

For instance, 5 – 3 = 2 and 3 – 5 = -2

Now, the “ABS” function works by not taking cognizance of the signs involved. Hence, it reads the value above as 2.

The basic function for this example is =IF(ABS(D7-E7)<=F7,"ACCEPT","REJECT")

The values in figure 5 are for an experiment whose values are allowed to differ within a tolerance level of 0.007. The only difference in applying this function is in ABS(D7-E7<=F7. After applying step 1, step 2 and you are at the last stage of step 3 in example 1 above, you should type the “ABS” function.

After that, open a bracket and then type the cells involved (cell 1- cell 2). Close the bracket and place the sign (<=). Then type the function of the cell showing the tolerance we are interested in; in the case of row 7, it is F7, row 8 is F8 and row 9 is F9. And, of course, are we ACCEPTING or REJECTING the values just like we did in Step 4 of example 1?

Still need some help with Excel formatting or have other questions about Excel? Connect with a live Excel expert here for some 1 on 1 help. Your first session is always free. 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar