tiny discrepancies in fixed-point numbers

[George]

My wife made a simple spreadsheet of fixed-point values. In the sample
below,
A = entered data
B = difference between successive rows of col A (2-1, 3-2, 4-3)
C = manually entered values for col B
D = B - C ... should be all 0.


A B C D
1 6874.61 -194.40 -194.40 -5.40E-13
2 7069.01 -64.80 -64.80 -1.85E-13
3 7133.81 -50.00 -50.00 0.00E+00
4 7183.81

.... the issue being, why are some results in col D not identically 0?

Compared vs rounded versions of themselves, Cols A and C are just as
written - there is no hidden 'tiny fractional part'. I have manually
re-typed parts of Cols A & C; this has not helped.

If it might matter, the cells were initially formatted as "general".
This has since been changed to "number", with 2 decimal places.

Thanks,
George

 

[joeu2004]

A = entered data
B = difference between successive rows of col A (2-1, 3-2, 4-3)
C = manually entered values for col B
D = B - C ... should be all 0.
A B C D
1 6874.61 -194.40 -194.40 -5.40E-13
2 7069.01 -64.80 -64.80 -1.85E-13
3 7133.81 -50.00 -50.00 0.00E+00
4 7183.81
... the issue being, why are some results in col D not identically 0?

This is very common problem. The explanation is very technical. But the
solution is straight-forward: use ROUND in expressions when you require
that the result be "exact" to a specific number of decimal places.

For example, instead of =A2-A1, write =ROUND(A2-A1,2).

The format of the cells do not make a difference, except that formatting
Number with 2 decimal places might __hide__ the problem. The underlying
value still has an infinitesimal difference.

(Note: I do __not__ recommend setting the "Precision As Displayed"
calculation option.)

See http://support.microsoft.com/kb/78113 for a full, albeit somewhat
incorrect explanation of the infinitesimal differences.

 

[joeu2004]

the solution is straight-forward: use ROUND in expressions when you
require that the result be "exact" to a specific number of decimal
places. For example, instead of =A2-A1, write =ROUND(A2-A1,2).

To explain.... Excel uses the computer's 64-bit binary floating-point to
represent numbers and perform arithmetic [2]. Consequently, most
non-integers cannot be represented exactly.

For example, try to represent 0.1 exactly using the sum of powers of 2:
1/16 + 1/32 + 0/64 + 0/128 + 1/256 + 1/512 + .... It cannot be done, even
with an "infinite" sum. And 64-bit binary floating-point is limited to a
sum of 53 consecutive terms.

In your case, the exact internal representations are on the right [1]:

6874.61 6874.60999999999,96725819073617458343505859375
7069.01 7069.01000000000,0218278728425502777099609375
=A2-A1 194.400000000000,5456968210637569427490234375
194.40 194.400000000000,005684341886080801486968994140625

You can see the difference between =A2-A1 and 194.40.

Using ROUND corrects these infinitesimal differences. But note that 194.40
still is not represented exactly.


-----
[1] You cannot see these exact internal representations because Excel
arbitrarily displays only up to 15 significant digits (to the left of the
comma above), rounding.

[2] Actually, the computer uses 80-bit binary floating-point to perform
arithmetic. But in Excel, each pairwise operation is converted to 64-bit
binary floating-point result.

 

[George]

the solution is straight-forward: use ROUND in expressions when you
require that the result be "exact" to a specific number of decimal
places. For example, instead of =A2-A1, write =ROUND(A2-A1,2).

To explain.... Excel uses the computer's 64-bit binary floating-point to
represent numbers and perform arithmetic [2]. Consequently, most
non-integers cannot be represented exactly.

For example, try to represent 0.1 exactly using the sum of powers of 2:
1/16 + 1/32 + 0/64 + 0/128 + 1/256 + 1/512 + .... It cannot be done, even
with an "infinite" sum. And 64-bit binary floating-point is limited to a
sum of 53 consecutive terms.

In your case, the exact internal representations are on the right [1]:

6874.61 6874.60999999999,96725819073617458343505859375
7069.01 7069.01000000000,0218278728425502777099609375
=A2-A1 194.400000000000,5456968210637569427490234375
194.40 194.400000000000,005684341886080801486968994140625

You can see the difference between =A2-A1 and 194.40.

Using ROUND corrects these infinitesimal differences. But note that 194.40
still is not represented exactly.


-----
[1] You cannot see these exact internal representations because Excel
arbitrarily displays only up to 15 significant digits (to the left of the
comma above), rounding.

[2] Actually, the computer uses 80-bit binary floating-point to perform
arithmetic. But in Excel, each pairwise operation is converted to 64-bit
binary floating-point result.

Thanks. I'm good.