In Excel why does the sum of these three cells come to zero?
-1000000000000000000
1
1000000000000000000
Clearly a negative number take plus a positive number (regardless of the
size) should cancel each other out and leave the answer as one...

"Illya Teideman" wrote in message
...
In Excel why does the sum of these three cells come to zero?
-1000000000000000000
1
1000000000000000000
Clearly a negative number take plus a positive number (regardless of the
size) should cancel each other out and leave the answer as one...

You are seeing a consequence of rounding when a number exceeds the
limitations of number precision. Excel, like nearly all software, uses what
are called IEEE Double Precision Floating Point numbers. This type of data
storage is limited to 15 digits of precision, which is the number of digits
both to the left and right of the decimal places that can be accurately
stored. When that limitation is reached, rounding occurs.

Your numbers are +/- 1 * 10^18, or 18 digits. Excel can't store that without
rounding. When you add

1000000000000000000 + 1,

the "+1" is rounded away because to store that level of precision, Excel
would need 18 digit precision, but Excel is limited to 15 digits. Change
the 1 to greater powers of 10 and you'll see various degrees of accuracy due
to rounding. For example, change it to 1000 then 10000 then 100000. You'll
see the accuracy get better until it is correct.

VBA has a data type called Decimal that has much higher precision. You will
get the correct result with code like

Dim D1 As Variant
Dim D2 As Variant
Dim D3 As Variant
Dim D4 As Variant

D1 = CDec("1E18")
D2 = CDec("-1E18")
D3 = CDec(1)
D4 = CDec(D1 + D2 + D3)
Debug.Print D4

You get the correct result here because a Decimal type can store numbers
with greater precision.

For a more in depth discussion of rounding, see
www.cpearson.com/Excel/Rounding.htm


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

Excellent chip you are a scholar a gentleman

"Chip Pearson" wrote:


"Illya Teideman" wrote in message
...
In Excel why does the sum of these three cells come to zero?
-1000000000000000000
1
1000000000000000000
Clearly a negative number take plus a positive number (regardless of the
size) should cancel each other out and leave the answer as one...

You are seeing a consequence of rounding when a number exceeds the
limitations of number precision. Excel, like nearly all software, uses what
are called IEEE Double Precision Floating Point numbers. This type of data
storage is limited to 15 digits of precision, which is the number of digits
both to the left and right of the decimal places that can be accurately
stored. When that limitation is reached, rounding occurs.

Your numbers are +/- 1 * 10^18, or 18 digits. Excel can't store that without
rounding. When you add

1000000000000000000 + 1,

the "+1" is rounded away because to store that level of precision, Excel
would need 18 digit precision, but Excel is limited to 15 digits. Change
the 1 to greater powers of 10 and you'll see various degrees of accuracy due
to rounding. For example, change it to 1000 then 10000 then 100000. You'll
see the accuracy get better until it is correct.

VBA has a data type called Decimal that has much higher precision. You will
get the correct result with code like

Dim D1 As Variant
Dim D2 As Variant
Dim D3 As Variant
Dim D4 As Variant

D1 = CDec("1E18")
D2 = CDec("-1E18")
D3 = CDec(1)
D4 = CDec(D1 + D2 + D3)
Debug.Print D4

You get the correct result here because a Decimal type can store numbers
with greater precision.

For a more in depth discussion of rounding, see
www.cpearson.com/Excel/Rounding.htm


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