Sep 14 2021 03:03 AM
Sep 14 2021 03:03 AM
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
Please advise am I miss anything.
Sep 14 2021 04:02 AM
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.
Sep 14 2021 03:21 PM - edited Sep 14 2021 03:28 PM
@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:
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(!).
Sep 14 2021 08:41 PM - edited Sep 14 2021 08:53 PMSolution
@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:
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.
Sep 15 2021 03:42 AM
As mentioned on my previous post you can do the following
Sep 15 2021 08:28 AM
@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.
Sep 15 2021 11:36 AM