Excel 2007 adding error

  • Thread starter Thread starter Erik Thorsteinsson
  • Start date Start date
E

Erik Thorsteinsson

If I add the following numbers one by one:

-726347.61
2300000
-673159.06
-485000
-485000
-2300000
-600000
-366457.33
-1850829.29
485000
600000
726347.61
485000
1850829.29
673159.06
366457.33

I get the results:

-726347,61
1573652,39
900493,33
415493,33
-69506,6699999999
-2369506,67
-2969506,67
-3335964
-5186793,29
-4701793,29
-4101793,29
-3375445,68
-2890445,68
-1039616,39
-366457,33
0

Can please anyone tell me where the ten decimal places in the fourth
subtotal come from?
 
Computer arithmetic is done in binary, not decimal. Most terminating decimal
fractions (including all 2-digit fractions other than .00, .25, .50, and .75)
are non-terminating binary fractions that can only be approximated (just as
1/3 can only be approximated as a decimal fraction). The discrepancy between
the approximation and its intended exact value is beyond Excel’s 15 digit
display limit, but subtraction can remove enough leading digits to make that
discrepancy visible.

Since you are only adding/subtracting numbers with no more than 2 decimal
places, you can round each result to 2 decimal places to reduce the
accumulated impact of these binary approximations without violence to the
intended exact calculations.

Jerry
 
I had figured that out in the meantime, but it does not change the fact that
it should not be necessary to round numbers to two decimal places which had
two decimal places to begin with.
I have been using Excel 2000 and 2003 almost every day for over ten years
now and never encountered anything like this before.
 
The part of my reply that you glossed over is the fact that this is not an
Excel bug; it is the fundamental nature of finite precision arithmetic (e.g.
1/3 cannot be exactly represented in decimal) that only surprises our
intuition because binary arithmetic exposes it in calculations that otherwise
seem innocuous. This happens with all software and hardware; has been well
known to programmers for more than half a century; and the fact that you have
never seen it before means that you have been surprisingly lucky.

No universal "fix" is possible, because appropriate steps to reduce the
accumulation of approximation discrepancies depend on the type of calculation
that you are doing. In many calculations intermediate rounding will greatly
reduce accuracy; it happens to be appropriate in your case because you are
only adding and subtracting numbers with a defined limit to the number of
decimal places. In general, knowledgeable programmers are responsible for
insuring that their calculations are appropriate in light of finite precision
arithmetic.

Jerry
 
Dear Jerry,

I read your answer carelully and understand completely what the problem is.
I agree that knowledgeable programmers are responsible for insuring that
their calculations are appropriate in light of finite precision arithmetic.
But the normal user is not a knowledgeable programmer and should not have to
worry about things like that.

It cannot be to much to ask, that Excel automatically recognizes numbers
with finite number of decimal places when exercising simple things like
addition and subtraction.

The fact that a highly sophisticated software like Excel delivers wrong
results when subtracting numbers with a finite number of decimal places, an
operation which the simplest pocket calculator never does wrong, is nothing
but ridiculous.
 
You are saying in effect that Excel should be able to recognize your intent
and react accordingly. I doubt that anyone at Microsoft (or anywhere else)
could program such a mind-reading module, and certainly not without bloating
the application to the point that it would no longer be fit for general use
on today's hardware.

The way calculations are processed is that two numbers are pulled from
memory and combined with the requested operator. The arithmetic processor
does not know how those two numbers came to be in their memory locations
(direct entry or the result of some string of calculations, much less which
string of calculations); and so has no way to know what is so obvious to you
and me: that at each step the first number is the sum/difference of numbers
that each had no more than 2 decimal places.

Without that knowledge, any distortion of the (currently correct)
arithmetic, that in your particular calculations may reduce the impact of
approximations to your intended input numbers, will necessarily degrade the
accuracy of some other calculations for which Excel might be employed.

IMHO it is far better to educate people, so that they can behave
knowledgeably, than to reduce the usefulness of a very friendly interface by
restricting it to a specialized set of calculations.

Jerry
 
Dear Jerry,

thank you very much for your patience and for explaining things.

Best regards,
Erik
 
You are very welcome. It can take a little time to get used to.

Integer arithmetic is exact on computers (as long as you avoid overflows),
but as soon as you involve decimal fractions, you need to be aware that you
are working with approximations to the numbers, so calculation results may
also only be approximately what you intended.

The easiest way to think about the implications of finite precision
arithmetic and decimal/binary conversions is to consider that anything beyond
Excel's documented display limit of 15 significant digits may be different
than you would expect. If you need to know more exactly what is happening,
you may find useful the VBA functions at
http://groups.google.com/group/microsoft.public.excel/msg/6efb95785d1eaff5

All the best,
Jerry
 
Back
Top