wrongly added or subtracted decimals

[Guest]

Suddenly I have started problem with simple calculations (addition and
subtraction). I have starting values in the first column wtih maximum two
significant figures. I then perform a few operations (addition and
subtraction) using numbers from a previous operation(s) for a following
operation. For instance, 1) the original value, 2.875, 2) 100-2.875, then 3)
the answer from 2) - 95.15. The final answers should be exactly 1.075, but
when I extend a number of decimal places, the value has a small value either
added or subracted from the supposed to be answers. For example, instead of
1.075, it shows 1.0750000000001200. I have tried to correct this error
without any success. Does anyone have any idea how to correct this problem?


none

 

[Bernard Liengme]

This is not YOUR problem; it results from the way computer convert decimal
numbers to binary.
See Excel IEEE error http://support.microsoft.com/kb/78113
Your solution is =ROUND(A1-B1, 10 ) to get 1.0750000000

by the way: what do you mean by "I have starting values in the first column
with maximum two significant figures"
The value 2.875 has 4 sig fid (or 3 decimal places) while 95.15 has 4 sig
figs (but 2 decimal places)

"Sig figs" is NOT a synonym for "number of decimal places"

Forgive an old professor!

 

[Guest]

Excel's math is correct, but most terminating decimal fractions (including
95.15 and 1.075) are non-terminating binary fractions that must be
approximated. When you do math with approximate inputs, it should be no
surprise when the output is also only approximate.

As Bernard has noted, the level of approximation is defined by IEEE 754
standard for double precision calculations (followed by almost all general
purpose software).

Using the VBA functions from
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
=D2D(95.15) gives 95.150000000000005684341886080801486968994140625 as the
exact value of the approximation to 95.15. Do the math; 97.125 minus this
value is 1.974999999999994315658113919198513031005859375 which Excel
correctly reports to its documented limit of 15 figures as 1.97499999999999.

Jerry