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.

 

[Andrew Poulsom]

What are the numbers?

 

[cb366374]

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. If you leave it out and just sum the first 4, it returns 0. EDIT: I'm working in 2010 right now if that matters.

 

[Andrew Poulsom]

An optimisation was introduced in Excel 97 that covers the first four numbers. I don't know why it wouldn't be used for all 5, sorry. The result is what it would have been without optimisation.

 

[joeu2004]

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. If you leave it out and just sum the first 4, it returns 0.

It's debatable which result is "incorrect". But suffice it to say: when you expect the result of arithmetic to be accurate to 3 decimal places (for example), it behooves you to explicitly round to that number of decimal places using the ROUND function.

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.

Thus, SUM({1.333, 1.225, -1.333, -1.225}) correctly returns about -2.22E-16 when formatted as General or Scientific, as does SUM({1.333, 1.225, -1.333, -1.225,0}).

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.

The inconsistency arises when those 5 numbers are in A1:A5, for example. Then, SUM(A1:A4) returns exactly zero (0.00E+0 when formatted as Scientific), whereas SUM(A1:A5) returns about -2.22E-16.

The difference is due to the dubious "close to zero" heuristic that is poorly documented in http://support.microsoft.com/kb/78113. 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 Microsoft does not specify the contexts in which it applies the heuristic. I can offer a theory, if you would like. Again, please let me know.

But the important take-away is: the well-intentioned heuristic is applied and implemented inconsistently. IMHO, it causes more confusion than it is worth.

Again, IMHO, it is better to live with the realities of IEEE binary representation and rely on the explicit use of ROUND to ensure results that are consistent with decimal arithmetic expectations (usually).

 

[cb366374]

joeu2004, if you read my original post, I'm not looking for a lesson on IEEE. Your comment just repeats the problem I already laid out. I completely understand the limitations of IEEE and I know it's not Excel's "fault." But when 4 numbers sum to 0, it should return 0 without having to round. It is incorrect to return anything else. Whether it's a limitation of Excel or IEEE is irrelevant, it's just wrong. Microsoft knows that, which is why they built in the adjustment to correct for this precision limitation. I had a very specific question regarding the inclusion of "0" in the range and why it breaks this "correction" implemented by Excel. But it doesn't seem like anyone knows exactly how that works exactly. If we knew how it worked, we might be able to find an alternative to using ROUND in all our formulas.

Actually, the temporary solution I like for my application is to use "<0.000001" instead of "=0" in my IF statements. But ROUND would probably be the best generic advice to give someone trying to solve this.

 

[joeu2004]

I completely understand the limitations of IEEE and I know it's not Excel's "fault." But when 4 numbers sum to 0, it should return 0 without having to round. It is incorrect to return anything else.

Then you don't "completely understand the limitations of IEEE". But since you're "not looking for a lesson on IEEE", I won't waste my time explaining.

I had a very specific question regarding the inclusion of "0" in the range and why it breaks this "correction" implemented by Excel. But it doesn't seem like anyone knows exactly how that works exactly.

There is no rational reason why adding or subtracting zero should affect the outcome. It is a defect. Actually, it is a design flaw.

Defects and design flaws cannot be explained. They just happen. At best, we can only explain the conditions that cause the defect or design flaw to appear.

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.

Moreover, generally the heuristic applies only to the explicit plus, minus and comparison operators, not to implicit operations within Excel functions.

SUM() is an exception. Note that SUMIF() behaves differently.

And the implementation of the heuristic in SUM() is particularly fickle. It does not consistently apply rule #3. In my example where A1:A5 contains 1.333, 1.225, -1.333, -1.225 and 0, SUM(A1:A5) is different from SUM(A1:A4,0).

No one can explain "how that works". It doesn't work! (Correctly.)

 

[cb366374]

x + (-x) + y + (-y) + 0 = 0

That statement should always be true (assuming real numbers). Anything else is incorrect. I don't care if it's the standard or the software, when that formula above is entered into an application, the result should be 0. I realize that's not easy to do in some cases. I'm not complaining. I am merely trying to ascertain what might be causing the bug. Most defects CAN be explained, otherwise we would never be able to fix them or create workarounds. It is weird that the order of the values matters but I noticed that too.

 

[shg]

That statement should always be true (assuming real numbers).

It certainly is true with real numbers. Alas, your computer uses an infinitesimal subset of rational numbers, which are in turn an infinitesimal subset of the reals.

At the end of the day, you can bark at the Moon, or learn workarounds that limit the adverse impact on the calculations you're trying to perform. That topic is the the subject of numerical analysis.

 

[cb366374]

That topic is the the subject of numerical analysis.

Which was my major in undergrad...I said I wasn't complaining, I have a very deep appreciation for what Excel is able to do. But that doesn't change the fact that the answer is incorrect, which is why we have to try to find workarounds/fixes. Users don't care how a computer calculates 2+2, they just want it to equal 4...always.

Anyway, I have another interesting "solution." If you can put {1,-1} in a couple cells somewhere (let's say G1:H1), you can add that to the end of the sum ranges and it seems to work. So instead of =SUM(A1:A5) put =SUM(A1:A5,G1:H1) and that gives you the correct answer, "0". But this does NOT work: =SUM(A1:A5,1,-1). Not sure if that's any better than ROUND or an altered IF statement but it's interesting nonetheless.