ABS function issue

[SomTony]

The following formula always show false when J6 is between 69.1-70 and I6 is 68 in Microsoft 365 16.0.14228.20216 but it shows correct answer = true in MS Office 2013 

=ABS((ROUNDDOWN(J6,2)-ROUNDDOWN(I6,2)))>=0.01

Please advise am I miss anything.

[#2]

@SomTony 

It looks okay for me

Check it by formula evaluation (Ribbon Formulas >> Evaluate formula)

One likelihood can be the Floating Point Precision "Error" caused by IEEE 754 Standard

But I guess it is not the case.

[#3]

@SomTony  wrote: ``The following formula always show false when J6 is between 69.1-70 and I6 is 68``

Please attach an Excel file (redacted) that demonstrates when the formula returns FALSE unexpectedly.

And attach __two__ files, one for Office 365 and one for Excel 2013, that demonstrate when the formula has different results for the "same" data.

Ostensibly, 69.1 - 68 is too large a difference for the comparison with 0.01 to be FALSE, even if we assume that J6 and I6 are not exactly the values that they appear to be.

With the smallest possible value in J6 and the largest possible value in I6, 69.05 - 68.5 = 0.55, not at all close to 0.01.  And that is even more true as we increase J6.

(FYI, the largest value in I6 is really 68.4999999999999 + 4.2632564145606E-14.)

Be that as it may....

-----

@Juliano-Petrukio  wrote: ``One likelihood can be the Floating Point Precision "Error" caused by IEEE 754 Standard. But I guess it is not the case.``

That is still a valid issue.  And the work-around is:

=ABS(ROUND(ROUNDDOWN(J6,2)-ROUNDDOWN(I6,2), 2))>=0.01

The problem is with the (binary) subtraction.  It has nothing to do with ABS.

In general, whenever we expect a calculation that involves or results in a decimal fraction to be accurate to some number of decimal places, we should explicitly round to that number of decimal places (and not to an arbitrary number like 10), even if the individual values are rounded properly.

For example, 10.01 - 10 = 0.01 returns FALSE(!).

[#4]

@Joe User 

Thanks for all of the replies.

Here is the testing.  The same formula for 68.1%-67.00% = 0.01, but 69.1%-68.00% = 0.0099999999

This is the point which I don't get it.

[#5 · Accepted solution]

@SomTony  wrote: ``The same formula for 68.1%-67.00% = 0.01, but 69.1%-68.00% = 0.0099999999``

Aha!  You are comparing 69% - 68% with 0.01, not 69 - 68.  You misspoke in the original posting.

I do not see any comparison of Office 365 Excel and Excel 2013 results.  So I assume that you realize now that they return the same results for exactly the same data.  The operative word is "exactly".

As to your question: why does ABS(...)>=0.01 return FALSE in most of your examples?

@Juliano-Petrukio already pointed you to a MSFT explanation, albeit unnecessarily complex and inaccurate, IMHO.   And as I noted previously, the problem has nothing to do with ABS.

More importantly, I already demonstrated the work-around, to wit:

=ABS(ROUND(ROUNDDOWN(B5,2)-ROUNDDOWN(A5,2), 2))>=0.01

or equivalently:

=ROUND(ABS(ROUNDDOWN(B5,2)-ROUNDDOWN(A5,2)), 2)>=0.01

The point is:  in most of your examples, the difference is not 0.01 (1%), but 0.00999999999999999 (approximately).

And obviously, 0.00999999999999999>=0.01 is FALSE.

So your question should be:  why is the difference 0.00999999999999999 instead of 0.01?

The simpler explanation (IMHO) is....

Excel uses 64-bit binary floating-point to represent numbers internally, and most decimal fractions cannot be represented exactly in that binary form.

Moreover, the binary approximation of a particular decimal fraction (e.g. 0.01) might vary, depending on the magnitude of the number.  That is why 10.01-10>=0.01 returns FALSE(!).

Excel complicates our ability to understand the problem because it arbitrarily limits the number of digits that it displays to up to 15 significant digits (rounded).  Consequently, there might be infinitesimal "residuals" that we cannot see by formatting.

In general, the work-around is.... When we expect a calculation that involves or results in a number with a decimal fraction to be accurate to some number of decimal places, we should explicitly round to that number of decimal places (and not to an arbitrary number like 10), even if the individual numbers appear to be rounded appropriately.

See columns D:H in the "sheet1 corrected" worksheet in the attached file.

LMK if you have further questions.

[#6]

@SomTony 

As mentioned on my previous post you can do the following

1. Click Microsoft Office Button -> Excel Options -> Advanced
2. In the When calculating this workbook section, select the workbook you want, and then select the Set precision as displayed check box

[#7]

@Juliano-Petrukio  wrote: ``select the Set precision as displayed``

That is strongly ill-advised, IMHO.

First, setting PAD does not fix @SomTony's problem because PAD applies only to the final cell value, not to values of intermediate expressions.

In other words, with PAD set, it is true that =ABS(ROUNDDOWN(B6,2)-ROUNDDOWN(A6,2)) in D6 results in exactly (the binary approximation of) 0.01, if D6 is formatted as Number with 2 decimal places, given A6 in 68% and B6 in 69.1%.

But ABS(ROUNDDOWN(B6,2)-ROUNDDOWN(A6,2))>=0.01 still returns FALSE because ABS actually returns 0.00999999999999999 (- 1.73472347597681E-18) in that context, and PAD does not "correct" it.

-----

Second, IMHO, it is ill-advised to set PAD without saving a copy of the file before setting PAD.

The reason is:  merely setting PAD might irrevocably change constants that are purposely formatted with less precision (e.g. interest rates); and such changes can affect the entire workbook.  Thus, if we decide later to deselect PAD, the "damage" might have been done already.

Fortunately, Excel gives us some warning of that side-effect when we set PAD.  Pay attention to it.

[#8]

We know about it. That's why a comprehensive and technical explanation was given on previous article. Excel has some hidden features like that for a reason. Was achieved the reason and the why. The warning is given before the user moves forward.
There are situations that such feature is required , or other situations to handle with iterations and circular references ....

But thank you for your concerns. Its important we share different experiences on the same technical problem.