Forum Discussion
ABS function issue
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.
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.
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.