=SUM returns 5.54365E-11 instead of 0 (zero)
 

I have a set of journal data extracted from SQL Server 2005 and paste them
in Excel 2003, the net result of this set of number should produce 0,
instead in the cell that I use the =SUM() function, it produced
"5.54365E-11"
How come?
I could format the cell using "Number" which gaved me "0" but I am just
wondering why?

=SUM returns 5.54365E-11 instead of 0 (zero)
 

It is a consequence of rounding numbers that cannot be exactly represented
in binary fashion. It is similar to the case of 1/3+1/3+1/3 < 1 with a
finite number of decimal places. No matter how many decimal places you use,
you'll always get 0.999..., not 1. Numbers are rounded at the very small
end. See www.cpearson.com/Excel/Rounding.htm for more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Chwan Keng Ng" wrote in message
...
I have a set of journal data extracted from SQL Server 2005 and paste them
in Excel 2003, the net result of this set of number should produce 0,
instead in the cell that I use the =SUM() function, it produced
"5.54365E-11"
How come?
I could format the cell using "Number" which gaved me "0" but I am just
wondering why?

=SUM returns 5.54365E-11 instead of 0 (zero)
 

Thanks, Chip.

From some of the tests that I did, it seems that even without rounding, BUT
with larger number (20,000,000 and above), both +ve and -ve, it produces the
"error". I suppose, as per your article, it could be the IEEE floating
point storage that cause the inconsistency?

CK


"Chip Pearson" wrote in message
...
It is a consequence of rounding numbers that cannot be exactly represented
in binary fashion. It is similar to the case of 1/3+1/3+1/3 < 1 with a
finite number of decimal places. No matter how many decimal places you
use,
you'll always get 0.999..., not 1. Numbers are rounded at the very small
end. See www.cpearson.com/Excel/Rounding.htm for more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Chwan Keng Ng" wrote in message
...
I have a set of journal data extracted from SQL Server 2005 and paste
them
in Excel 2003, the net result of this set of number should produce 0,
instead in the cell that I use the =SUM() function, it produced
"5.54365E-11"
How come?
I could format the cell using "Number" which gaved me "0" but I am just
wondering why?

=SUM returns 5.54365E-11 instead of 0 (zero)
 

It is not inconsistent, merely unexpected. Presumably you understand why
non-terminating decimal fractions (such as in Pi, or Chip's example of 1/3)
must be approximated, because they cannot be represented as a finite decimal.
The only surprise here is that most terminating decimal fractions are non
terminating binary fractions that also must be approximated.

I will guess that "journal" refers to a financial journal with numbers to no
more than 2 decimal places. The only 2-place decimal fractions that have
exact binary representations are .00, .25, .50, and .75; the rest must be
approximated. When you do math with approximate inputs, it should not be
surprising that the output only approximates the result of calculation with
exact inputs.

As Chip's link describes, the approximation used by Excel (and almost all
other general purpose computer software) uses 53 bits, which corresponds
roughly to 15 digit accuracy. Larger numbers will have less accuracy for the
decimal fraction portion of numbers. Thus, you should think of
20,000,000.10
as
20,000,000.1000000?????
for accuracy purposes. In fact, its IEEE double precision approximation is
20,000,000.10000000149...
as shown by the D2D function available from
http://groups.google.com/group/micro...06871cf92f8465

Jerry


"Chwan Keng Ng" wrote:

Thanks, Chip.

From some of the tests that I did, it seems that even without rounding, BUT
with larger number (20,000,000 and above), both +ve and -ve, it produces the
"error". I suppose, as per your article, it could be the IEEE floating
point storage that cause the inconsistency?

CK


"Chip Pearson" wrote in message
...
It is a consequence of rounding numbers that cannot be exactly represented
in binary fashion. It is similar to the case of 1/3+1/3+1/3 < 1 with a
finite number of decimal places. No matter how many decimal places you
use,
you'll always get 0.999..., not 1. Numbers are rounded at the very small
end. See www.cpearson.com/Excel/Rounding.htm for more details.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Chwan Keng Ng" wrote in message
...
I have a set of journal data extracted from SQL Server 2005 and paste
them
in Excel 2003, the net result of this set of number should produce 0,
instead in the cell that I use the =SUM() function, it produced
"5.54365E-11"
How come?
I could format the cell using "Number" which gaved me "0" but I am just
wondering why?

=SUM returns 5.54365E-11 instead of 0 (zero)
 

Mon, 16 Jul 2007 04:46:00 -0700 from Jerry W. Lewis
:
It is not inconsistent, merely unexpected. Presumably you understand why
non-terminating decimal fractions (such as in Pi, or Chip's example of 1/3)
must be approximated,


Just a note -- pi and 1/3 are very, very different animals. It's true
they're both nonterminating decimals, but 1/3 is a fraction and its
decimal shows a pattern, where pi cannot be represented by a fraction
and its decimal shows no pattern.

Mathematicians call pi an irrational number because there is no
fraction (ratio of whole numbers) that can represent it. 1/3 is an
example of a rational number.


--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/

=SUM returns 5.54365E-11 instead of 0 (zero)
 

"Stan Brown" wrote...
....
Mathematicians call pi an irrational number because there is no
fraction (ratio of whole numbers) that can represent it. 1/3 is an
example of a rational number.
....

Mathematicians usually call pi a transcendental number to distinguish it
from algrbraic numbers like SQRT(2) and other countable sets of irrational
numbers.

=SUM returns 5.54365E-11 instead of 0 (zero)
 

Wed, 18 Jul 2007 01:27:19 -0700 from Harlan Grove
:
"Stan Brown" wrote...
...
Mathematicians call pi an irrational number because there is no
fraction (ratio of whole numbers) that can represent it. 1/3 is an
example of a rational number.
...

Mathematicians usually call pi a transcendental number to distinguish it
from algrbraic numbers like SQRT(2) and other countable sets of irrational
numbers.

You're correct, pi is a member of the transcendentals, a subset of
the irrationals.

I had thought about mentioning that in my earlier article, but I was
afraid I was already getting too mathematical. :-)

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com/