Forum Discussion
ABS function issue
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.
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.
7 Replies
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(!).
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.
As mentioned on my previous post you can do the following
- Click Microsoft Office Button -> Excel Options -> Advanced
- In the When calculating this workbook section, select the workbook you want, and then select the Set precision as displayed check box
It looks okay for me
Check it by formula evaluation (Ribbon Formulas >> Evaluate formula)
One likelihood can be the https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers/#:~:text=The%20IEEE%20754%20floating%2Dpoint,can%20be%20used%20in%20calculations.&text=This%20number%20cannot%20be%20represented,17%20when%20it%20is%20stored.
But I guess it is not the case.
