Precision IEEE sum to 0 error

[cb366374]

I have this spreadsheet with 5 months of data. For reasons unknown to me, 3 months have data that add up to 0 and the remaining 2 months are 0's. When summing the 3 numbers, Excel is smart enough to account for the floating-point issues and it returns 0. However, if you include the two 0's in the sum, it returns the 3.55xxxE-15 or whatever. So I'm just wondering if someone knows how Excel adjusts for that situation and why it breaks when you just add a 0 to the sum. Thanks.

 

[joeu2004]

I am merely trying to ascertain what might be causing the bug.

I am struggling to understand what alleged defects that you mentioned have not been explained.

First you wrote:

I'm just wondering if someone knows how Excel adjusts for that situation and why it breaks when you just add a 0 to the sum.

I explained that is part of the flawed design of the Excel "close to zero" heuristic. I wrote:

In some limited contexts, Excel arbitrarily considers two values as equal if they are "close enough"; and it considers their difference to be exactly zero if the actual difference is "close enough".

Microsoft does not define "close enough".

And I wrote:

In part, generally the heuristic applies (1) only to the last arithmetic operation, (2) only to addition of operands with opposite signs and to subtraction and comparison of operands with the same sign, and (3) only when both operands are non-zero.

Adding or subtracting zero violates rule #3.

I believe that completely explains "how Excel adjusts for that situation" and "why it breaks when you just add a 0".

Answers:

1. when Excel deems the result is "close enough" to zero; and
2. because the last operation is not an addition of non-zero operands with opposite signs, nor a difference between non-zero operands with the same sign.

-----

Later you wrote:

You can replicate it using these numbers {1.333, 1.225, -1.333, -1.225, 0}. If you include the 0 in your sum you get an incorrect value.

I explained:

Generally, most non-integers cannot be represented exactly in binary. And the representation of arithmetic results is limited to a fixed number of binary digits ("bits"); specifically, the sum of 53 consecutive powers of 2 times an exponential factor.
[....]
If you would like a detailed explanation for why that is the "correct" result due to the way that Excel represents number and performs arithmetic, I can break it down for you. Please let me know.

You responded dismissively: "I'm not looking for a lesson on IEEE. [....] I completely understand the limitations of IEEE".

Obviously, you do not. But that response blocked a more complete explanation for why =A1+A2-A1-A2+0 returns about -2.22E-16, not zero, when A1 is 1.333 and A2 is 1.225.

Nevertheless, I did demonstrate that is the correct result within the limitations of IEEE binary representation by noting that SUM({1.333, 1.225, -1.333, -1.225}) returns about -2.22E-16, not zero.

In other words, it is not that adding zero returns an "incorrect" value. Instead, it is that adding zero blocks Excel from changing the true arithmetic result within the limitations of IEEE binary representation.

To reinforce that point, note the following additional examples:

=(A1+A2-A1-A2) returns about -2.22E-16

and

=IF(10.1 - 10 = 0.1, TRUE) returns FALSE

(The first example defeats the "close to zero" heuristic because subtraction is no longer the last operation. In Excel's flawed design, the right parenthesis is!)

Both results are correct within the limitations of IEEE binary representation.

I believe that explains, to the extent that you permitted, why "you get an incorrect value" (non-zero).

Answer: It is correct within the limitations of IEEE binary representation.

-----

Granted, they are not the expected results based on decimal representation; and usually they are not the desired result.

And granted, there are reasonable algorithms and methods that Excel could implement to ameliorate some undesired effects of the limitations of IEEE binary representation. (The "close to zero" heuristic is not one of them.)

But Excel does not.

 

[cb366374]

I believe that completely explains "how Excel adjusts for that situation" and "why it breaks when you just add a 0".

Answers:

1. when Excel deems the result is "close enough" to zero; and
2. because the last operation is not an addition of non-zero operands with opposite signs, nor a difference between non-zero operands with the same sign.

This is not an explanation of WHY. This is just laying out in what scenarios the error occurs, which I already knew.

You responded dismissively: "I'm not looking for a lesson on IEEE. [....] I completely understand the limitations of IEEE".

Obviously, you do not.

What are you talking about? I'm not concerned with the limitations of IEEE 754, I AM VERY FAMILIAR. And your condescending replies are not helpful. If you re-read my comments, I'm asking about the specific example I had come up, not the standard. I don't care that you can re-explain the same problem I laid out with more detail. I don't care about the issues expressing 0.1 in binary, that's well-documented. I'm asking WHY Excel isn't smart enough to apply the same correction when 0 is the last operand. And your answer to that question is because 0 is not non-zero? Umm that's helpful.

Again, I don't care if something is "correct within the limitations of IEEE binary representation" and no users will ever care either. I'm building models for people to use on a daily basis and when this issue comes up they're not going to say, "Oh, that's actually correct, it's just a limitation of IEEE 754." It will be more like, "WTF is wrong with this model?"

I appreciate the help but maybe I just haven't been clear in what I was asking. Your point about the 0 being the last operand is an important observation that I had also noticed while testing but I am looking for WHY it can't be 0 and if there is a better workaround than adding the {1,-1} range to the end or using ROUND. Excel should be smart enough to correct for the last operand being 0. A bug is a bug but sometimes you can improve upon the workarounds which is all I am trying to do.