Excel 2003 addition/substraction formula giving unexpected values

[John A Owen]

I am adding accounting information to Excel 2003. I have three columns which
for each entry contain the amount debited (Column G), amount credited (Column
H)and running balance (Column I). As I am incorporating data from a number
of spreadsheets I have created a new column (Column J) which contains the
following formula (in cell J4 for example):

=(I3 - G4 + H4 - I4)

In most cases this should equal exactly zero, but in some cases the
calculation is not zero (I know this because I have conditional formatting
checking for values in column J not equal to zero). The value contains a
very small number. Here's a snippet from the spreadsheet:

G H I J
1 Debit Credit Balance Check Check to 20 dec. places
2 £62.53 £1,034.67 £0.00 0.00000000000000000000
3 £6.50 £1,028.17 £0.00 0.00000000000000000000
4 £9.99 £1,018.18 £0.00 0.00000000000011368684
5 £17.00 £1,001.18 £0.00 0.00000000000000000000
6 £37.20 £963.98 £0.00 -0.00000000000011368684
7 £150.00 £813.98 £0.00 0.00000000000000000000
8 £676.43 £137.55 £0.00 0.00000000000005684342
9 £5.97 £131.58 £0.00 0.00000000000000000000
10 £122.14 £9.44 £0.00 0.00000000000001243450
10 £150.00 £159.44 £0.00 0.00000000000000000000

This all looks very good, except despite all values in columns G, H and I
being currency values to two decimal places or blank, the check Column (J) in
some instances does not equal exactly zero.

Does anyone know why this is so?

Many thanks in advance,
John.

 

[Jerry W. Lewis]

Your concern appears to be that
=(1028.17-9.99-1018.18)
returns 1.13686837721616E-13 instead of 0. The math is correct; you are
seeing the impact of unavoidable approximations to the inputs
(approximations in give approximations out). In your application,
nothing beyond the 2nd decimal place is meaningful, so round all balance
and check calculations to 2 decimal places to avoid surprises.

The underlying issue is finite precision. Your intuition might relate
better to the following calculations in 4 decimal place decimal (such as
the VBA currency data type)
zero = (2/3) - (1/3) - (1/3)
Since each fraction can only be represented to 4 decimal places, this
would become
zero = 0.6667 - 0.3333 - 0.3333 = 0.0001

You are probably wondering what this has to do with your problem, but
Excel (and almost all other general purpose software) does binary math
and most decimal fractions (including .17, .99, and .18) have no exact
binary representation and must be approximated in finite precision (just
as thirds in decimal). Your final subtraction is
1018.180000000000063664629124104976654052734375 (approx 1028.17-9.99)
-1018.1799999999999499777914024889469146728515625 (approx to 1018.18)
-------------------------------------------------
0.0000000000001136868377216160297393798828125
which you can manually verify is correct, given the inputs. You cannot
directly see these approximations, because (as documented) Excel will
not display more than 15 digits, but the subtraction reveals evidence of
the approximations that were already there.

If you want to learn more about the (IEEE standard) approximations
involved, you might start with
http://www.cpearson.com/excel/rounding.htm

A simple way to think about the issues are to recognize that most
decimal fractions (integers can be represented exactly, provided they
involve less than 16 digits) may differ from your expectation beyond the
15th decimal place. Therefore you could think of your problem as
1028.17000000000?????
-9.99000000000000??
-1018.18000000000?????
----------------------
0.00000000000?????
which is consistent with the result Excel gave you.

Since Excel97, MS has further muddied the water by trying to hide the issue
http://support.microsoft.com/kb/78113?#XSLTH3196121122120121120120
with the result that
=(1028.17-9.99-1018.18)
and
=1028.17-9.99-1018.18
do not return the same result. The second version apparently recognizes
that the final operation is a subtraction between numbers that agree to
15 decimal places and arbitrarily zeros the result. Thus if you had used
=I3 - G4 + H4 - I4
instead of
=(I3 - G4 + H4 - I4)
you would not have seen these rounding residues until they had
accumulated to a greater degree where they would have been even harder
to describe and therefore diagnose. I agree with Kahan
http://www.cs.berkeley.edu/~wkahan/Mindless.pdf
that introducing this fuzz factor has avoided an easy question at the
expense of much more difficult and confusing questions.

It is not clear to me how this is causing Excel to crash. Otherwise the
"Worksheet Function" or "General Question" groups would be more
appropriate locations, that would have gotten you a much faster response.

Jerry

 

[John A Owen]

Thanks Jerry, for your comprehensive reply. I'll use the round function as
suggested.

 

[Jerry W. Lewis]

You're welcome. Glad it helped.

Jerry