Bug in excel fundamantal add-function

[snooze]

Hi

Where can I report a bug in excel? OK I try this group.

Try this

A1 = -621,2
A2 = 2
A3 = 1
A4 = -540,2
A5 = 175
A6 = 986,4
A7 = SUM(A1:A6)

A7 must be 3 - but try to have 13 decimals...!!! is this a BUG? I think
so

Using Danish XP and danish 2002 SP3

 

[JE McGimpsey]

No, it's not a bug. It's a "feature" of XL using IEEE double precision
floating point math. Most numbers can't be exactly represented in a
finite number of binary digits, just as 1/3 can't be exactly represented
by a finite number of decimal digits.

You can use ROUND() to round to zero if it's necessary.

See

http://cpearson.com/excel/rounding.htm

 

[Dave Peterson]

It's how binary sytems deal with number approximations:

http://www.mcgimpsey.com/excel/pennyoff.html

(From J.E. McGimpsey's site)

 

[snooze]

thanks for hints!!

 

[Jerry W. Lewis]

As previously noted, most decimal fractions cannot be represented exactly in
binary, although all integers (through 15 digits) can be represented exactly.

An easy way to predict the magnitude of binary approximations is to note
Excel's documented limit of 15 digits. Thus you can think of your problem as
-621,200000000000???
2
1
-540,200000000000???
175
986,400000000000???
--------------------
3,000000000000???
consistent with Excel's reported result of
2,99999999999989

For more precise information, see the D2D function at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
which shows that the binary approximations to 621,2 and 540,2 are larger
than their nominal values and the binary approximation to 986,4 is smaller
than its nominal value, hence the reason that magnitude of the accumulated
approximations is as large as it is.

Jerry

 

[Boy_alone_18]

can you explain to me why the sum() of 2 decimal numbers can have 13 decimal

like

(A1) 5384754,23
(A2) 1294830,46
(A3) 1583569,98
(A4) 2506349,21
(A5) 3,45
(A8) =A1-A2-A3-A4-A5

(A8=) 1,130000001

all this number are finite number.

thank,s

"JE McGimpsey" a écrit :

 

[Ron Rosenfeld]

Briefly, it is because Excel does not add using decimal arithmetic.

can you explain to me why the sum() of 2 decimal numbers can have 13 decimal

like

(A1) 5384754,23
(A2) 1294830,46
(A3) 1583569,98
(A4) 2506349,21
(A5) 3,45
(A8) =A1-A2-A3-A4-A5

(A8=) 1,130000001

all this number are finite number.

thank,s

"JE McGimpsey" a écrit :

--ron

 

[Jerry W. Lewis]

Less briefly, all of the numbers in A1:A5 have non-terminating binary
representations (just as 1/3 has a non-terminating decimal representation),
and hence must be approximated. The addition is exactly correct, given the
unavoidable approximations to your inputs.

Given the source of the information in the 13th decimal place, rounding it
away does no violence to this calculation. MS cannot do that for you,
because it would not be appropriate for all calculations. Excel (and almost
all other software) follows the IEEE standard for binary representation of
numbers. This is the general nature of almost all sofware, and not unique to
Excel.

If you want to see the actual values of the approximations employed, use the
D2D function at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465

Jerry