"Alan" wrote:
Extract from the original post :-
All the numbers (the PO amt and ea. invoice amt) are directly keyed into the
spreadsheet and are not created by formulas, so the issue is not related to
format vs. actual cell contents.
Thanks. I did overlook that. I also overlooked this statement in the original post, which is the key:
Interestingly, if I limit the formula to H = A1-B1-C1-D1 just accounting
the 3 invoice amounts, the result is fine, and yet if I enter a 0.00 into
the last 3 cells, it still gives the strange result.
The answer is given in the aforementioned reference,
http://support.microsoft.com/kb/78113, to wit:
"Excel 97, however, introduced an optimization that attempts to correct for this problem. Should an addition or subtraction operation result in a value at or very close to zero, Excel 97 and later will compensate for any error introduced as a result of converting an operand to and from binary. "
What they really mean is: if the __last__ addition or subtraction is "close" to zero; and of course, they neglect to define what "close" is.
For the expression A1-B1-C1-D1, the last subtraction is "close" to zero, to Excel "corrects" the value.
If we put parentheses around it, i.e. (A1-B1-C1-D1), we get the same non-zero result as the expression A1-B1-C1-D1-0 because the "last subtraction" does not "result" in a value close to zero. I guess Excel really means: the last operations results in a __change__ that is close to zero. In the first case, "-" is not considered the last operation because of the ")". (Sigh.)
(I have ever been enamored with this heuristic because it is so poorly defined and implemented, IMHO.)
So, to explain what is really going on, with the exception of when Excel's heuristics get in the way....
The problem, again as explained by the aforementioned references, is that most decimal fractions cannot be represented exactly in binary. This yields infinitesimal numerical "errors" (abberations) in nearly all non-integer numerical computations. The existence and magnitude of the abberations depend on the relative size of the operands, i.e. the values used in the computation.
In this case, 13,530.90 and $14,369.10 are really represented internally exactly as
13530.8999999999,99636202119290828704833984375 and
14369.1000000000,00363797880709171295166015625 , where the comma is my way of demarcating 15 significant digits to the left.
Compounding the "problem" (existence of computational abberations) is that when Excel performs the arithmetic, each pairwise operation is rounded to its internal representation. (FYI, this is not true in VBA expressions. So the same expression in VBA might have different results, even ignoring Excel's heuristics.)
So you cannot always work with even those exact values on paper and expect to get the same result exactly. For example:
X = A1-B1 is exactly 26264.0999999999,985448084771633148193359375 .
Y = X -C1 is exactly 14369.0999999999,985448084771633148193359375 .
Z = Y-D1 is exactly -0.00000000000181898940354585,6475830078125 before Excel's heuristic is applied.
As I said before, ROUND(A1-B1-C1-D1-E1-F1-G1,2) remedies the problem in this case.
By the way, so does A1-SUM(B1:G1) without rounding, as does A1-(B1+C1+D1+E1+F1+G1). This is because the sum of B1:G1 results in exactly 39795, even without Excel's heuristic. But that is coincidental; it might not be true for another set of numbers. So we should not rely on such ordering anomalies.
Just do the right thing and either use ROUND prolificly, or use the "Precision as displayed" option (with lots of caution).
HTH.
----- original posting -----
"Alan" wrote in message ...
Extract from the original post :-
All the numbers (the PO amt and ea. invoice amt) are directly keyed into the
spreadsheet and are not created by formulas, so the issue is not related to
format vs. actual cell contents.
Regards,
Alan.
"JoeU2004" wrote in message
...
"Ginger" wrote:
Using Excel 2003 and have discovered what I can only assume is a glitch.
If by "glitch", you mean defect, no. But it is indeed a common problem.
The formula in column H = A1-B1-C1-D1-E1-F1-G1.
This formula works great in all my rows (several hundred), EXCEPT when
the numbers we $39,795.00 - $13,530.90 - $11,895.00 - $14,369.10
[....].
The result (or the amount left to bill) should be $0.00, but instead Excel
told me the result was: -$0.00000000000181898940354586000.
Most likely, the numbers you show above are not exactly the values in the
cells. Format the cells as Number with 10 decimal places to see 15
significant digits.
As I said, this is a common problem. The usual remedy is one of two: (a)
always round formulas to 2 decimal places (or an appropriate number of
decimal places), even if you are simply adding and subtracting; or (b) set
the calculation option "Precision as displayed" under Tools Options
Calculation.
I lean toward #a because #b can have some unexpected results. But I must
admit: #a can be tedious and error-prone.
For further reference see:
http://support.microsoft.com/kb/78113/en-us
http://support.microsoft.com/kb/42980
http://support.microsoft.com/kb/214118
http://www.cpearson.com/excel/rounding.htm
----- original posting -----
"Ginger" wrote in message
...
Using Excel 2003 and have discovered what I can only assume is a glitch.
Here's the set-up: We recieve a PO for a project (col A), and then bill
the
customer each month for 6 months (cols. B-G), or until the project is
complete. Each month, I need to know how much is left to bill on each
project (col. H)
The formula in column H = A1-B1-C1-D1-E1-F1-G1.
This formula works great in all my rows (several hundred), EXCEPT when the
numbers we $39,795.00 - $13,530.90 - $11,895.00 - $14,369.10 (there
were
only 3 billings for this PO.)
The result (or the amount left to bill) should be $0.00, but instead Excel
told me the result was: -$0.00000000000181898940354586000.
All the numbers (the PO amt and ea. invoice amt) are directly keyed into
the
spreadsheet and are not created by formulas, so the issue is not related
to
format vs. actual cell contents.
Interestingly, if I limit the formula to H = A1-B1-C1-D1 just accounting
for
the 3 invoice amounts, the result is fine, and yet if I enter a 0.00 into
the
last 3 cells, it still gives the strange result.
I've experimented with the formula in many ways, but the issue seems to be
tied to subtracting the 1st entry with the .90, because when changing the
order and switching the first and second values it works fine.
I invite others to try this and see if you don't get the same thing.
Ultimately, I can just override the formula on this row, but it is
aggravating to have it not work properly.