Discrepancy in calculations with dollars and cents

[Eric]

In Excel 2003, I'm having an odd problem. I built the spreadsheet myself,
and didn't do anything funny, as far as I know. The numbers consist of
dollars and cents, and I'm doing nothing more than adding and subtracting.
I'm subtracting two numbers that look identical in the sheet (both 63868.84),
and I expect to get zero. But instead, I get -6.54836E-11.

I've been using comma format with two decimals (using the comma tool on the
formatting toolbar). I've tried reformatting with Number-General format to
see if there are any tiny numbers in the 11th decimal place, but I still see
only dollars and cents.

What could be happening here, and how can I fix it???

Thanks.

 

[joeu2004]

In Excel 2003, I'm having an odd problem. I built the spreadsheet myself,
and didn't do anything funny, as far as I know. The numbers consist of
dollars and cents, and I'm doing nothing more than adding and subtracting.
I'm subtracting two numbers that look identical in the sheet (both 63868.84),
and I expect to get zero. But instead, I get -6.54836E-11.
[....]
What could be happening here, and how can I fix it?

In part, the answer is: this is an well-known anomaly of binary
computer arithmetic.

However, the judicial use of ROUND(...,2), where "..." is your
formula, might mitigate the problem.

For example, you say that you are "subtracting two numbers that LOOK
identical in the sheet". If those numbers are derived by a formula,
you might try rounding each formula. For example:

B1: =round(A1/A2, 2)
B2: =round(A2*B1, 2)
B3: =A1-B2

I would be tempted to even write =round(A1-B2,2). I suspect (but
don't know to be true) that Excel might take steps to mitigate binary
computer arithmetic anomalies when ROUND() is used.

I've been using comma format with two decimals (using the comma tool on the
formatting toolbar). I've tried reformatting with Number-General format to
see if there are any tiny numbers in the 11th decimal place, but I still see
only dollars and cents.

Changes in formatting have no bearing in the actual underlying value.
They only affect what is displayed.

By the way, there is an option to do arithmetic as displayed (Tools =>
Options => Calculation => Precision As Displayed. That might
eliminate the problem, at least to a large degree.

I do not recommend that option because it affects all calculations in
the worksheet, which often is not truly desirable, although you might
not realize it now.

Moreover, nothing can completely eliminate the anomalies with binary
computer arithmetic. The use of ROUND or Calculation Precision As
Displayed are merely tools that tell Excel to do what it can to
mitigate the problem.

Decimal fractions are stored as the sum of a finite number of powers
of 1/2. The fact is: most decimal fractions cannot be represented
exactly in this manner. (This also affects the representation of
extremely large integers.) Moreover, because intermediate arithmetic
might be inexact for that reason, even arithmetic that mathematically
should result in an exact power of 1/2 (or a sum of such powers) might
not be represented exactly.

The only sure way to deal with it is to do what you can to mitigate
the problem (i.e. the judicial use of ROUND) and to never test for
equality or inequality, but to test for something "close". For
example, instead of IF(A1=A2,...), try IF(ABS(A1-A2)<0.005,...).

Purists might quibble with 0.005, since that cannot be represented
exactly in binary. But it is intended to be simply something "close
enough", not necessarily exactly a value that is rounded to 0.01.

If you want to test for the equality of the __displayed__ values, you
could do something like IF(TEXT(A1,"0.00")=TEXT(A2,"0.00"),...). But
that is usually overkill, IMHO.

HTH.

 

[Eric]

Thanks. Your explanations are quite explicit and I understand everything you
say.

But I have to admit I'm amazed to discover that Excel can't handle dollars
and cents satisfactorily, even when the operations are limited to addition
and substraction. Bookkeeping is such a standard thing that people want to
do, and I suspect that most non-IT people would not suspect that Excel can't
handle simple addition without problems or special options.

People may know that 1/3 does not equal .33333333...., But most people do
not know that 1/10 does not equal .1, or that 12345.67 + .01 does not equal
12345.68. Couldn't Excel offer a decimal data type as an attribute of a
number, rather than an option that applies to the whole workbook?

Anyway, thanks for helping me figure this out.

In Excel 2003, I'm having an odd problem. I built the spreadsheet myself,
and didn't do anything funny, as far as I know. The numbers consist of
dollars and cents, and I'm doing nothing more than adding and subtracting.
I'm subtracting two numbers that look identical in the sheet (both 63868.84),
and I expect to get zero. But instead, I get -6.54836E-11.
[....]
What could be happening here, and how can I fix it?

In part, the answer is: this is an well-known anomaly of binary
computer arithmetic.

However, the judicial use of ROUND(...,2), where "..." is your
formula, might mitigate the problem.

For example, you say that you are "subtracting two numbers that LOOK
identical in the sheet". If those numbers are derived by a formula,
you might try rounding each formula. For example:

B1: =round(A1/A2, 2)
B2: =round(A2*B1, 2)
B3: =A1-B2

I would be tempted to even write =round(A1-B2,2). I suspect (but
don't know to be true) that Excel might take steps to mitigate binary
computer arithmetic anomalies when ROUND() is used.

I've been using comma format with two decimals (using the comma tool on the
formatting toolbar). I've tried reformatting with Number-General format to
see if there are any tiny numbers in the 11th decimal place, but I still see
only dollars and cents.

Changes in formatting have no bearing in the actual underlying value.
They only affect what is displayed.

By the way, there is an option to do arithmetic as displayed (Tools =>
Options => Calculation => Precision As Displayed. That might
eliminate the problem, at least to a large degree.

I do not recommend that option because it affects all calculations in
the worksheet, which often is not truly desirable, although you might
not realize it now.

Moreover, nothing can completely eliminate the anomalies with binary
computer arithmetic. The use of ROUND or Calculation Precision As
Displayed are merely tools that tell Excel to do what it can to
mitigate the problem.

Decimal fractions are stored as the sum of a finite number of powers
of 1/2. The fact is: most decimal fractions cannot be represented
exactly in this manner. (This also affects the representation of
extremely large integers.) Moreover, because intermediate arithmetic
might be inexact for that reason, even arithmetic that mathematically
should result in an exact power of 1/2 (or a sum of such powers) might
not be represented exactly.

The only sure way to deal with it is to do what you can to mitigate
the problem (i.e. the judicial use of ROUND) and to never test for
equality or inequality, but to test for something "close". For
example, instead of IF(A1=A2,...), try IF(ABS(A1-A2)<0.005,...).

Purists might quibble with 0.005, since that cannot be represented
exactly in binary. But it is intended to be simply something "close
enough", not necessarily exactly a value that is rounded to 0.01.

If you want to test for the equality of the __displayed__ values, you
could do something like IF(TEXT(A1,"0.00")=TEXT(A2,"0.00"),...). But
that is usually overkill, IMHO.

HTH.

 

[Eric]

(Sorry if this posts multiple times. I'm having technical problems.)

P.S. You may be interested in some of the background. The story is
apparently more complicated. Apparently Excel will treat appoximately equal
numbers as equal up till a certain tolerance, beyond which it will treat them
as not equal. At least that's what I think I'm observing.

The numbers that I'm comparing were calculated by simple addition and
subtraction. It's essentially a checkbook register, where the running
balance in each row is calculated from the previous balance (in the previous
row), plus or minus the entry for the current row (i.e., new ballance is
calculated as previous balance plus current entry if a credit, or minus
current entry if a debit). The debits and credits are simply constants
entered into the cells, not calculated values. I've done a check for
"equals" for various rows (vs. the balance number on the bank statement), and
the problem doesn't appear till row 101, so apparently Excel does do
something to fudge the "equals" comparison. But apparently when the
discrepancy gets to be above a certain tiny amount, the "equals" test begins
to return FALSE. At least that's what I think I'm seeing.

I typed this into a cell: =1/100=.01, and got TRUE. So Excel apparently
does know how to do approximate calcs.

I thought you'd be interested in this. Thanks again.

In Excel 2003, I'm having an odd problem. I built the spreadsheet myself,
and didn't do anything funny, as far as I know. The numbers consist of
dollars and cents, and I'm doing nothing more than adding and subtracting.
I'm subtracting two numbers that look identical in the sheet (both 63868.84),
and I expect to get zero. But instead, I get -6.54836E-11.
[....]
What could be happening here, and how can I fix it?

In part, the answer is: this is an well-known anomaly of binary
computer arithmetic.

However, the judicial use of ROUND(...,2), where "..." is your
formula, might mitigate the problem.

For example, you say that you are "subtracting two numbers that LOOK
identical in the sheet". If those numbers are derived by a formula,
you might try rounding each formula. For example:

B1: =round(A1/A2, 2)
B2: =round(A2*B1, 2)
B3: =A1-B2

I would be tempted to even write =round(A1-B2,2). I suspect (but
don't know to be true) that Excel might take steps to mitigate binary
computer arithmetic anomalies when ROUND() is used.

I've been using comma format with two decimals (using the comma tool on the
formatting toolbar). I've tried reformatting with Number-General format to
see if there are any tiny numbers in the 11th decimal place, but I still see
only dollars and cents.

Changes in formatting have no bearing in the actual underlying value.
They only affect what is displayed.

By the way, there is an option to do arithmetic as displayed (Tools =>
Options => Calculation => Precision As Displayed. That might
eliminate the problem, at least to a large degree.

I do not recommend that option because it affects all calculations in
the worksheet, which often is not truly desirable, although you might
not realize it now.

Moreover, nothing can completely eliminate the anomalies with binary
computer arithmetic. The use of ROUND or Calculation Precision As
Displayed are merely tools that tell Excel to do what it can to
mitigate the problem.

Decimal fractions are stored as the sum of a finite number of powers
of 1/2. The fact is: most decimal fractions cannot be represented
exactly in this manner. (This also affects the representation of
extremely large integers.) Moreover, because intermediate arithmetic
might be inexact for that reason, even arithmetic that mathematically
should result in an exact power of 1/2 (or a sum of such powers) might
not be represented exactly.

The only sure way to deal with it is to do what you can to mitigate
the problem (i.e. the judicial use of ROUND) and to never test for
equality or inequality, but to test for something "close". For
example, instead of IF(A1=A2,...), try IF(ABS(A1-A2)<0.005,...).

Purists might quibble with 0.005, since that cannot be represented
exactly in binary. But it is intended to be simply something "close
enough", not necessarily exactly a value that is rounded to 0.01.

If you want to test for the equality of the __displayed__ values, you
could do something like IF(TEXT(A1,"0.00")=TEXT(A2,"0.00"),...). But
that is usually overkill, IMHO.

HTH.

 

[joeu2004]

But I have to admit I'm amazed to discover that Excel can't handle dollars
and cents satisfactorily, even when the operations are limited to addition
and substraction. Bookkeeping is such a standard thing that people want

It is even more suprising when you consider that the progenitor of all
spreadsheet programs, Visicalc, was designed specifically to mimic the
manual spreadsheets that accountants maintained on paper at the time.
It did little more.

Couldn't Excel offer a decimal data type as an attribute of a
number, rather than an option that applies to the whole workbook?

Absolutely! COBOL had a decimal data type. In fact, Visicalc used
decimal arithmetic when it was introduced on the Apple II. I don't
know what spreadsheet product first introduced floating-point
arithmetic and when. But I suspect it was not until FP processors
were standard in PCs, about 8 years after the introduction of the IBM
PC.

I have read in these newsgroups that there are Excel add-ins that
provided decimal arithmetic. Perhaps someone will provide information
in a follow-up. If not, search for it or start a new thread.

 

[Bernard Liengme]

Your problems might be solved by using the option Set Precision to As
Displayed.
But I found ROUND to be a better solution.
best wishes

 

[Jerry W. Lewis]

Specifically, Excel introduced an "optimization" in Excel 97
http://support.microsoft.com/kb/78113
where a subtraction involving two numbers that are equal to 15 decimal
digits will arbitrarily return zero if that subtraction is the final
operation. IMHO, this has caused more confusion than it has solved, since
=a-b may return zero, when
=IF(a-b=0,"equal","not equal") returns "not equal" and when =(a-b) returns
a nonzero value.

Decimal arithmetic is not a panacea here, since it is still subject to
finite precision approximations as you have already noted. The advantage is
that the approximations occur where your intuition expects them to. The
price is slower calculations, higher memory and storage requirements, and
slightly less accuracy when approximations occur. Since you would need to
keep your wits about you anyway, why not do it in the most efficient system?

Also note that this is not an "Excel issue" per se, since almost all
hardware and software (Excel is only one example) follow the IEEE standard
for double precision binary representation of numbers.

For you application (only adding and subtracting numbers with no more than 2
decimal places) rounding results to 2 decimal places is the best solution,
since anything beyond that is necessarily the result of binary approximations.

Jerry

(Sorry if this posts multiple times. I'm having technical problems.)

P.S. You may be interested in some of the background. The story is
apparently more complicated. Apparently Excel will treat appoximately equal
numbers as equal up till a certain tolerance, beyond which it will treat them
as not equal. At least that's what I think I'm observing.

The numbers that I'm comparing were calculated by simple addition and
subtraction. It's essentially a checkbook register, where the running
balance in each row is calculated from the previous balance (in the previous
row), plus or minus the entry for the current row (i.e., new ballance is
calculated as previous balance plus current entry if a credit, or minus
current entry if a debit). The debits and credits are simply constants
entered into the cells, not calculated values. I've done a check for
"equals" for various rows (vs. the balance number on the bank statement), and
the problem doesn't appear till row 101, so apparently Excel does do
something to fudge the "equals" comparison. But apparently when the
discrepancy gets to be above a certain tiny amount, the "equals" test begins
to return FALSE. At least that's what I think I'm seeing.

I typed this into a cell: =1/100=.01, and got TRUE. So Excel apparently
does know how to do approximate calcs.

I thought you'd be interested in this. Thanks again.

In Excel 2003, I'm having an odd problem. I built the spreadsheet myself,
and didn't do anything funny, as far as I know. The numbers consist of
dollars and cents, and I'm doing nothing more than adding and subtracting.
I'm subtracting two numbers that look identical in the sheet (both 63868.84),
and I expect to get zero. But instead, I get -6.54836E-11.
[....]
What could be happening here, and how can I fix it?

In part, the answer is: this is an well-known anomaly of binary
computer arithmetic.

However, the judicial use of ROUND(...,2), where "..." is your
formula, might mitigate the problem.

For example, you say that you are "subtracting two numbers that LOOK
identical in the sheet". If those numbers are derived by a formula,
you might try rounding each formula. For example:

B1: =round(A1/A2, 2)
B2: =round(A2*B1, 2)
B3: =A1-B2

I would be tempted to even write =round(A1-B2,2). I suspect (but
don't know to be true) that Excel might take steps to mitigate binary
computer arithmetic anomalies when ROUND() is used.

I've been using comma format with two decimals (using the comma tool on the
formatting toolbar). I've tried reformatting with Number-General format to
see if there are any tiny numbers in the 11th decimal place, but I still see
only dollars and cents.

Changes in formatting have no bearing in the actual underlying value.
They only affect what is displayed.

By the way, there is an option to do arithmetic as displayed (Tools =>
Options => Calculation => Precision As Displayed. That might
eliminate the problem, at least to a large degree.

I do not recommend that option because it affects all calculations in
the worksheet, which often is not truly desirable, although you might
not realize it now.

Moreover, nothing can completely eliminate the anomalies with binary
computer arithmetic. The use of ROUND or Calculation Precision As
Displayed are merely tools that tell Excel to do what it can to
mitigate the problem.

Decimal fractions are stored as the sum of a finite number of powers
of 1/2. The fact is: most decimal fractions cannot be represented
exactly in this manner. (This also affects the representation of
extremely large integers.) Moreover, because intermediate arithmetic
might be inexact for that reason, even arithmetic that mathematically
should result in an exact power of 1/2 (or a sum of such powers) might
not be represented exactly.

The only sure way to deal with it is to do what you can to mitigate
the problem (i.e. the judicial use of ROUND) and to never test for
equality or inequality, but to test for something "close". For
example, instead of IF(A1=A2,...), try IF(ABS(A1-A2)<0.005,...).

Purists might quibble with 0.005, since that cannot be represented
exactly in binary. But it is intended to be simply something "close
enough", not necessarily exactly a value that is rounded to 0.01.

If you want to test for the equality of the __displayed__ values, you
could do something like IF(TEXT(A1,"0.00")=TEXT(A2,"0.00"),...). But
that is usually overkill, IMHO.

HTH.

 

[joeu2004]

Decimal arithmetic is not a panacea here, since it is still subject
to finite precision approximations as you have already noted.

I beg to differ. The operative word is "here". If the operations are
merely addition and subtraction, as they are "here", decimal
arithmetic would be accurate to two decimal places (and even more),
which is all that is required "here". Even multiplication would be
accurate (up to some huge number), and division can be made to be
accurate when rounding is used judiciously, at least for accounting
applications.

The price is slower calculations, higher memory and storage
requirements, and slightly less accuracy when approximations occur.

That was certainly a reasonable trade-off 25-30 years occur, when
microprocessor instructions were still 0.5 to 1 usec, a "big"
microprocessor system sported 640 KB of memory, and transistor
switching times were 5 nsec.

Now, __instruction__ times are 5 nsec or less, and most systems have
512 MB, if not 1 GB, of memory. (Of course, the O/S and concurrent
processes gobble up a lot more memory, too. But that probably still
leaves at least 500 to 1000 times more memory in today's systems for
your application.)

I imagine that the vast majority of Excel applications would not
notice the speed degradation due to implementing decimal arithmetic in
software. And if they did, that might push the industry toward
providing decimal arithmetic hardware as an option, if not built-in,
just as the emerging need for floating-point arithmetic did in the
1970/80s.

Besides, we are merely talking about the __option__ to perform decimal
arithmetic. Use it only if you need it.

Since you would need to keep your wits about you anyway, why not
do it in the most efficient system?

And you don't need to keep your wits about you, arguably even more,
when using the current binary arithmetic standard? How many times a
week does this issue come up in these newsgroups?

Excel already has the dubious feature to perform calculations to the
"precision as displayed". Frankly, I think the use (or not) of that
feature requires far more "wits".

(Note: I don't know how "precision as displayed" is implemented.
Perhaps you do. I always ass-u-me-d that it still does binary
arithmetic on intermediate results, merely making adjustments at the
end. But now I wonder if that option invokes decimal arithmetic
routines.)

Also note that this is not an "Excel issue" per se, since almost all
hardware and software (Excel is only one example) follow the IEEE
standard for double precision binary representation of numbers.

I certainly understand what you are trying to say. Yes, the problem
is pervasive. But to absolve the application (Excel) of
responsibility is like saying that jagged lines and a high-contrast
color variation is not an issue for professional graphics products to
solve. I am sure you would be singing a very tune if your inkjet
printer did not incorporate software and firmware to ameliorate those
natural side-effects of pixelization.

It is true that Excel has grown beyond merely being an accounting
tool. But for accounting uses, there is no excuse for Excel not to
provide a solution that ensures accounting accuracy.

(After-thought: As I noted in my question above, perhaps "precision
as displayed" is indeed that solution. But for some reason, I did not
think so.)

The advantage is that the approximations occur where your
intuition expects them to.

You said it the best.

The above are just my opinions. Reasonable people can disagree.

 

[joeu2004]

Errata....

I beg to differ. The operative word is "here". If the operations are
merely addition and subtraction, as they are "here", decimal
arithmetic would be accurate to two decimal places (and even more),
which is all that is required "here". Even multiplication would be
accurate (up to some huge number), and division can be made to be
accurate when rounding is used judiciously, at least for accounting
applications.

Aarrgghh!! Overzealous editing altered the intent and correctness of
what I meant to say. I should have stopped at saying....

The operative word is "here". If the operations are merely addition
and subtraction, as they are "here", decimal arithmetic would be
accurate to two decimal places (and even more), which is all that is
required "here".

 

[Jerry W. Lewis]

As I previously noted, there are any number of ways to accurately do
addition/subtraction with a fixed number of decimal places. That would of
course include decimal rather than binary arithmetic.

You seem to have realized the accuracy problems that arise once you admit
multiplication/division, so I won't belabor that point.

I assume that Excel currently uses hardware for as many calculations as
possible (4 function arithmetic, logs, exponentials, trig functions, etc).
Aside from the logic of not "reinventing the wheel", this assumption seems to
be supported by the fact that AFAIK all questions of arithmetic correctness
in Excel have turned out to be due to one of four issues
- failure of the reporter to understand how IEEE binary calculations are
supposed to work
- failure of Excel (early versions of Excel 97) to recalculate fully
- failure of Excel to correctly display correctly calculated results
(appears to be fixed by latest 2007 SP)
- Excel (2007 only?) updating the display before it completes recalculation

With the trend toward larger and larger worksheets (particularly with the
new larger limits allowed in 2007), you may be underestimating the speed
penalty of switching from binary hardware math to decimal software math.

I wouldn't hold my breath, but it is possible that you could interest MS in
writing their own decimal math routines even though that will only "solve"
the problem when people stick to addition/subtraction of fixed decimal place
numbers in realtively small workbooks. If they did, I would personally
hesitate to use it because of the level of validation required.

Jerry

 

[joeu2004]

I assume that Excel currently uses hardware for as many calculations as
possible (4 function arithmetic, logs, exponentials, trig functions, etc).

I think it is fair to say that all bets are off when "higher math"
functions like log, exp, and trig functions are involved. After all,
those are implemented with approximation formulas anyway, so no one
can have any expectation of the precision of the result. However, I
would expect those functions to convert from and back to decimal, if
indicated by the user.

With the trend toward larger and larger worksheets (particularly with the
new larger limits allowed in 2007), you may be underestimating the speed
penalty of switching from binary hardware math to decimal software math.

I am not underestimating the penalty. Obviously binary arithmetic
hardware is faster than software -- binary or decimal -- and it might
even be faster than decimal arithmetic hardware. Although with the
advent of RISC technology, that is hard to say. Perhaps it would just
take more hardware components -- that is, real estate on a chip --
than binary arithmetic hardware.

I am not underestimating the penalty. I am merely saying that the
vast majority of Excel users would not notice the difference. Neither
of us can prove or disprove such claims. It is just an opinion.

Using faster computers to permit less efficient implementations (a
trade-off for some other benefit) is not a new concept. When I
started in computing, we counted every assembly language instruction,
not only for space, but also for execution time. Now, almost no one
gives any thought about whether an assembly language implementation
might be faster than C (or C++ or C## or whatever).

I wouldn't hold my breath, but it is possible that you could interest MS in
writing their own decimal math routines even though that will only "solve"
the problem when people stick to addition/subtraction of fixed decimal place
numbers in realtively small workbooks.

I wouldn't hold my breath about interesting MS in anything. I don't
think they listen to their customer base. We are a captive audience,
and they know it.

But for the record, I am not just talking about addition and
subtraction; obviously, multiplication and division, too. There might
also be a handful of other operations that would make sense to
implement in a decimal library. I have not given it much thought
because I am not (any longer) in a position to influence such things.