0

I'm using Excel to highlight differences in two outputs that should be the same. My problem is that even if they're different by a value of 0.001 (which is technically correct), I want to add a threshold that caters for small amounts like this. So if A1 and B1 are within 0.001 of each other, for example, then it's true. Any more than this and then it becomes false.

The cells are also being rounded so currently look like this:

=ROUND(AJ4,6)=ROUND(T4/100,6)

Any help would be appreciated! Thanks.

1

One approach is to check the absolute value against a threshold, something like:

=ABS(ROUND(AJ4,6)-ROUND(T4/100,6)) < 0.001

The threshold value has to be chosen knowing the magnitude of values being compared.

You might compute the threshold based on the actual values, something like:

=(ROUND(AJ4,6)+ROUND(T4/100,6))/(2000)

This gives one-thousandth of the average of the two values, but may give a divide by zero.

Combining all the these you might try:

=if(ROUND(AJ4,6)=ROUND(T4/100,6),true,ABS(ROUND(AJ4,6)-ROUND(T4/100,6))< (ROUND(AJ4,6)+ROUND(T4/100,6))/(2000))

But using all these ROUND calls may hinder the evaluation, so it may be better as:

=if(ROUND(AJ4,6)=ROUND(T4/100,6),true,ABS(AJ4-T4/100)<(AJ4+T4/100)/(2000))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.