Is this a known bug in Excel ???

[Eoin C. Bairéad]

A lollypop for the best explanation:

paste the following column of figures into Column A of a blank worksheet,
starting in Cell A1

-64,828,933.36
-3,655.36
-151,908,847.10
-120,235.94
-271,265,832.86
-20,850.65
10,969,885.68
536,563,682.83
805,932.42
-10,969,885.68
-6,691.29
-11,672.18
-3,348.39
-2,057.24
3,109,597.44
-3,109,597.44
334.39
146,670.52
15,548.00
23,269.68
185.37
1.16
37,500.00
-49,421,000.00
x

yup - that's x in cell A25.

In Cell A26 put the formula
=SUM(A1:A25)

the total is 0 - zero.

then format the cells with custom mask:
#,##0.00;-#,##0.00;"zero".

so you now see the word zero appear in the total cell.

Now replace the x in A25 with 0 - zero.

The word zero appears in A25
but A26 is now 0.00.

Why ???

In addition, if you expand A26 to about 7 or 8 decimal places, digits start
appearing.


What IS happening ???????????



Eoin Bairéad
Dublin, Ireland
(e-mail address removed)

 

[AlfD]

Hi Eoin!

And there's more fun to be had.

Put a blank in for the x --> 0.00.....00
Put a 1 in for x --> 1.00000000745058.

There seem to be fractional bits lurking which can be released b
integers and/or suppressed by non-numbers.

I look forward to an explanation!

Al

 

[Charlie]

The sum is not equal to zero. Try rounding when summing
the column

=Round(Sum(A1.A25),2)

Charlie O'Neill

 

[AlfD]

Charlie:

Seriously:

Format A26 to 20 d.p.
Put x or space or nothing in A25: result 0.0000000000000.... to 2
d.p.
Put 0 in A25: result 0.00000000745058059692383.

It certainly surprises me, since I cannot connect the integer (sam
happens with 1,2..) with a change in the decimal part of the number.

Al

 

[Charlie]

Alf.

You are right, I quess I didn't go far enough when looking
at the problem. I just saw the 0.00000000745058059692383
and assumed it was just not rounding. I'll need to pay
more attention.

Thanks

Charlie O'Neill

 

[Jerry W. Lewis]

Excel and almost all other software does math in binary, specifically
using the IEEE standard for double precision storage, which resolves 15
decimal digits (see Help for "Excel specifications and limits" subtopic
"Calculation specifications"). None of your nontrivial decimal
fractions can be exactly represented in binary (much as 1/3 has no exact
finite decimal representation). Since the computer can only approximate
your inputs, it should be no surprise that the output is only an
approximation.

An easy way to think of your problem without the gory details of decimal
to binary conversion is to consider all digits past the 15th as
uncertain, and trace through that uncertainty.

-64828933.3600000??????????
-3655.36000000000??????
-151908847.100000???????????
-120235.940000000????????
-271265832.900000???????????
-20850.6500000000???????
10969885.6800000??????????
536563682.800000???????????
805932.420000000????????
-10969885.6800000??????????
-6691.29000000000??????
-11672.1800000000???????
-3348.39000000000??????
-2057.24000000000??????
3109597.44000000?????????
-3109597.44000000?????????
334.390000000000?????
146670.520000000????????
15548.0000000000???????
23269.6800000000???????
185.370000000000?????
1.16000000000000???
37500.0000000000???????
-49421000.0000000??????????
0.00000000000000???
----------------------------
0.000000???????????
for which Excel returns
0.00000000745058059692383
which is an accurate 15 digit representation of the exact answer
0.000000007450580596923828125
to the sum of the binary approximations to your numbers:

The nonzero answer to your sum is entirely consistent with the IEEE
standard. The surprise is not that you get the correct nonzero answer,
but that under some circumstances you get a zero answer.

Since your data are in A1:A25 with 0 in A25. Examine the results of the
following three formulas
= SUM(A1:A23)+A24
=(SUM(A1:A23)+A24)
= SUM(A1:A23)+A24+A25
I interpret these results to mean that when the last operation is a
subtraction (or adding a negative number) that involves the difference
of two numbers that are not exactly equal, but are equal to 15 decimal
digits, then Excel arbitrarily zeroes the result. Such a fuzz factor is
used in many software packages, and is not unreasonable, since with such
an operation it is often the case that the inequality is an artifact of
accumulated binary approximations. The fuzz factor is not applied if
the subtraction is not the final operation, to avoid introducing
inaccuracy (beyond the unavoidable finite precision binary
approximations) in chain calculations. Both wrapping the equation in
parentheses, or adding an additional number (even if it is exactly zero).

This issue with binary digital floating point calculations has been well
known for over half a century (long before MS existed). The standard
technique for dealing with it is to not test for equality, but to test
whether the difference from the target value is sufficiently small. An
alternate method, based on knowledge of your particular problem would be
to round the final result to 2 decimal places. Since no input goes
beyond 2 decimal places, and you are only adding and subtracting,
anything beyond the second decimal place is an artifact of binary
approximations.

Jerry
Excel MVP

 

[Eoin C. Bairéad]

If Mr Lewis would be kind enough to forward an appropriate address, a
lollypop is on the way.

Otherwise I will send a no-email e-lollypop to the address beneath!


btw - does lollypop mean the same on both sides of the Atlantic ???

Eoin Bairéad
Dublin, Ireland
(e-mail address removed)

 

[Jerry W. Lewis]

In the U.S.A., a lollypop is a hard candy disk on a rolled paper stick.
I can't speak to the meaning in Ireland.

Jerry Lewis
Wilmington, Massachusetts