Re: Discrepancy in calculations with dollars and cents
- From: Eric <Eric@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sun, 18 Nov 2007 17:36:01 -0800
(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.
"joeu2004" wrote:
On Nov 18, 2:51 pm, Eric <E...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:.
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.
- Follow-Ups:
- Re: Discrepancy in calculations with dollars and cents
- From: Jerry W. Lewis
- Re: Discrepancy in calculations with dollars and cents
- References:
- Re: Discrepancy in calculations with dollars and cents
- From: joeu2004
- Re: Discrepancy in calculations with dollars and cents
- Prev by Date: Re: Discrepancy in calculations with dollars and cents
- Next by Date: Re: Password Protect Macros
- Previous by thread: Re: Discrepancy in calculations with dollars and cents
- Next by thread: Re: Discrepancy in calculations with dollars and cents
- Index(es):
Relevant Pages
|
