How to compare numbers but allowing a tolerance in Excel?
In Excel, we can apply the formula: =A1=B1 to return a TRUE or FALSE to identify if the two compared cells are equal, but, sometimes, we need to compare two numbers but allowing a tolerance. For example, I have the following data, column A and column B, to compare them with a plus or minus tolerance of 5, that is to say if cell A1 is more or less than cell B1 5 number, it will get a TRUE, otherwise, get a FALSE as following screenshot shown. To solve this problem, this article may help you.
The following formulas can help you to return a TRUE or FALSE when you compare two numbers with a tolerance, please do as this:
1. Enter any of the following formulas into a blank cell beside your compared cells, see screenshot:
Note: In these formulas: A1 and B1 is the two numbers that needed to be compared, and the number 5 indicates the tolerance range of the two number, you can change them to your need.
2. Then drag the fill handle down to the cells that you want to apply this formula, and it will return a TRUE if the two numbers are in a specified tolerance range, or else, FALSE is displayed, see screenshot:
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 3 months agoif i use this formula =AND(A1<=B1+5,A1>=B1-5) instead of it equalling True if it passes is it possible to get it to equal another cell
- To post as a guest, your comment is unpublished.· 10 months agoif between 100mm,90mm tolerance is 0.3 excel formulas
- To post as a guest, your comment is unpublished.· 1 years agoI am trying to set up a formula that says if A is +5% of another number, then run a calculation. However, the above are only helping in comparison. Any ideas?
- To post as a guest, your comment is unpublished.· 3 years agoHi, i need a solution in excel with formula for,
If in column value is .550 ± .030 and when i entered a value in next column if entered value is with in that limit then it should highlight in one color and if out off limit it should highlight in red color.