Simple subtraction formula returning strange results = Excel glitc
 

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.

Simple subtraction formula returning strange results = Excel glitc
 

I've duplicated the formula and figures and it returns 0.00 every time. I've
changed the order of the figures and entered 0.00 in E1, F1 and G1, but as I
say, it always returns 0.00. Send me the file if you want to, I'm curious.
Regards,
Alan.
bill24777(at)hotmail.co.uk
(Email address is typed that way to avoid spam bombardment!)
"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.

Simple subtraction formula returning strange results = Excel glitc
 

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.

Simple subtraction formula returning strange results = Excel glitc
 

"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.

Simple subtraction formula returning strange results = Excel glitc
 

"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.

Simple subtraction formula returning strange results = Excel g
 

While
=39795-13530.9-11895-14369.1
returns zero,
=(39795-13530.9-11895-14369.1)
does not. This confusing twist is due to an unfortunate "optimization" that
MS introduced in Excel 97
http://support.microsoft.com/kb/78113
where the result any final subtraction arbitrarily returns zero if it
involves two numbers that are equal to the documented display limit of 15
decimal digit. Wrapping the expression in parentheses or any function (such
as IF), concatenating a string, etc. means that it no longer fits the
conditions of this "optimization"

As noted in JoeU's references, the issue is not a problem with Excel's
arithmetic, but the inability of binary to to exactly represent most decimal
fractions (and hence an issue with all binary computer hardware and software,
not just Excel). The only 2-digit decimal fractions with exact
representations are .00, .25, .50, and .75.

The easiest way to think about it is to consider that anything beyond the
15th figure may be different than expected, hence the OP's calculation
becomes (the following looks better in a fixed pitch font)
39795.00
-13530.9000000000???
-11895.00
-14369.1000000000???
--------------------
0.0000000000???
which is perfectly consistent with Excel's displayed result of
0.0000000000018...

That thought process is adequate to understand how to protect yourself from
surprises. If you want to know exactly what is going on under the hood, you
may find useful the VBA functions at
http://groups.google.com/group/micro...fb95785d1eaff5

Jerry

"Alan" wrote:

I've duplicated the formula and figures and it returns 0.00 every time. I've
changed the order of the figures and entered 0.00 in E1, F1 and G1, but as I
say, it always returns 0.00. Send me the file if you want to, I'm curious.
Regards,
Alan.
bill24777(at)hotmail.co.uk
(Email address is typed that way to avoid spam bombardment!)
"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.

Simple subtraction formula returning strange results = Excel glitc
 

Excel's math (non-zero result) is right given the initial binary
approximations to $13,530.90 and $14,369.10. The confusion caused by
=A1-B1-C1-D1 being different from =A1-B1-C1-D1-E1-F1-G1 where the last 2
cells contain zero is due to an unfortunate "optimization" that MS introduced
in Excel 97, whereby final subtractions are set to zero if the two numbers
are equal to 15 decimal digits. With =A1-B1-C1-D1-E1-F1-G1 the final
subtraction takes zero from the returned result, and so the "optimization"
does not kick in.

You are less likely to run into the problem with
=A1-(B1+C1+D1+E1+F1+G1)
or more simply
=A1-SUM(B1:G1)
but the more foolproof solution is to round the result to 2 decimal places.
This does no violence to the intent of your particular calculations, and is
safe since the underlying math is correct.

Jerry

"Ginger" wrote:

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.

Simple subtraction formula returning strange results = Excel g
 

Wow - thanks JoeU, Jerry and Alan for the help!!
Honestly, I'll have to re-read your answers in order to (better) understand
them, but you have really made my day to know that there is logic behind what
happened!
As I stated, I had planned to override the formula in this row and move on,
but just needed to know there was a reason for it acting so strangely.
I noticed the problem in the first place, because in col. I, I have an IF
statement: IF(H1=0,"COMPLETE","still active"). So, knowing this project was
complete, and already billed in full, I didn't know why col. I was saying
"Still active".
That's when I expanded the decimals to see that it was indeed not 0.
I will try your options for fixing the formula, and re-post to tell you how
it worked.

Simple subtraction formula returning strange results = Excel g
 

JoeU and Jerry, thanks again for your help - maybe you can further clarify
one point.
I have read your answers and explored the links you provided (and spent way
too much time on this, I'm sure!) And I get the whole binary number thing
(who knew), but I think it is the "opitmization" part that is throwing me.
Basically, to use another example:
.5 - .4 - .1 = 0,
then why does .5 - .4 - .1 - 0 = -.0000000000000000277555756...
I assume the first example which returned 0, "really" returns the odd value,
but Excel has "optimized" the result and so corrects the answer to be 0.(?)
What I don't get is why the second example which only has the extra
operation of - 0 at the end, doesn't also get "optimized/corrected"?


"Ginger" wrote:

Wow - thanks JoeU, Jerry and Alan for the help!!
Honestly, I'll have to re-read your answers in order to (better) understand
them, but you have really made my day to know that there is logic behind what
happened!
As I stated, I had planned to override the formula in this row and move on,
but just needed to know there was a reason for it acting so strangely.
I noticed the problem in the first place, because in col. I, I have an IF
statement: IF(H1=0,"COMPLETE","still active"). So, knowing this project was
complete, and already billed in full, I didn't know why col. I was saying
"Still active".
That's when I expanded the decimals to see that it was indeed not 0.
I will try your options for fixing the formula, and re-post to tell you how
it worked.

Simple subtraction formula returning strange results = Excel g
 

No problem. A concrete example should help cement the concepts for you.

Using the D2D VBA function that I recommended, you will see that 0.5 is
represented exactly (2^-1 obviously has an exact binary representation),
while the approximations to 0.4 and 0.1 are
0.400000000000000022204460492503130808472633361816 40625
0.100000000000000005551115123125782702118158340454 1015625
If you do the math, you will see that =0.5-0.4 (using the approximation to
0.4) is exactly
0.099999999999999977795539507496869191527366638183 59375
which with Excel's 15 digit display limit displays as 0.1, even though the
exact value is different than the value you got by entering 0.1 directly.
Thus, the exact result of
=0.5-0.4-0.1-0 or =(0.5-0.4-0.1),
which is also the value used by
=IF(0.5-0.4-0.1=0, €¦
is
-2.77555756156289135105907917022705078125E-17
But, since the two different values that both display as 0.1 agree to 15
decimal digits, the "optimization" kicks in for
=0.5-0.4-0.1
and returns 0, even though the two numbers are not exactly equal.

I suspect that the seemingly inconsistent math that results from this
"optimization" has produced more questions than the "optimization" avoids,
hence my contention that the "optimization" was an unfortunate decision.

Jerry

"Ginger" wrote:

JoeU and Jerry, thanks again for your help - maybe you can further clarify
one point.
I have read your answers and explored the links you provided (and spent way
too much time on this, I'm sure!) And I get the whole binary number thing
(who knew), but I think it is the "opitmization" part that is throwing me.
Basically, to use another example:
.5 - .4 - .1 = 0,
then why does .5 - .4 - .1 - 0 = -.0000000000000000277555756...
I assume the first example which returned 0, "really" returns the odd value,
but Excel has "optimized" the result and so corrects the answer to be 0.(?)
What I don't get is why the second example which only has the extra
operation of - 0 at the end, doesn't also get "optimized/corrected"?


"Ginger" wrote:

Wow - thanks JoeU, Jerry and Alan for the help!!
Honestly, I'll have to re-read your answers in order to (better) understand
them, but you have really made my day to know that there is logic behind what
happened!
As I stated, I had planned to override the formula in this row and move on,
but just needed to know there was a reason for it acting so strangely.
I noticed the problem in the first place, because in col. I, I have an IF
statement: IF(H1=0,"COMPLETE","still active"). So, knowing this project was
complete, and already billed in full, I didn't know why col. I was saying
"Still active".
That's when I expanded the decimals to see that it was indeed not 0.
I will try your options for fixing the formula, and re-post to tell you how
it worked.

Simple subtraction formula returning strange results = Excel g
 

"Ginger" wrote:
Basically, to use another example:
.5 - .4 - .1 = 0,
then why does .5 - .4 - .1 - 0 = -.0000000000000000277555756...
I assume the first example which returned 0, "really" returns the odd
value,
but Excel has "optimized" the result and so corrects the answer to be
0.(?)
What I don't get is why the second example which only has the extra
operation of - 0 at the end, doesn't also get "optimized/corrected"?

You have learned your lessons well, Young Skywalker :-). Seriously, you
understand this better than most people, and perhaps better than you think.

The short answer is: Excel's implementation of the adjustment is terribly
flawed. That is compounded by the fact that MS's explanation of adjustment
is terribly flawed as well.

The following is my best explanation, deduced from experiments:

If the last operation is addition or subtraction that results in a change
from the intermediate result of the previous operations, and if the result
of the last operation is "close" to zero, Excel will return exactly zero.
Otherwise, Excel will return the exact result of the operations. Note that
a closing parenthesis (")") is considered an operation in this context.
(Sigh.)

Even that explanation is flawed insofar as it does not define what "close"
is. That's MS's fault.

In your first example, the last operation is subtraction (-0.1) that results
in a change from the previous intermediate result (0.1), and Excel
apparently deemed the result from the last subtraction to be "close" to
zero.

In your second example, the last operation is subtraction (-0), but it does
not result in a change from the previous intermediate result. So apparently
Excel does not bother to see if the result is "close" to zero.


----- original message -----

"Ginger" wrote in message
...
JoeU and Jerry, thanks again for your help - maybe you can further clarify
one point.
I have read your answers and explored the links you provided (and spent
way
too much time on this, I'm sure!) And I get the whole binary number thing
(who knew), but I think it is the "opitmization" part that is throwing me.
Basically, to use another example:
.5 - .4 - .1 = 0,
then why does .5 - .4 - .1 - 0 = -.0000000000000000277555756...
I assume the first example which returned 0, "really" returns the odd
value,
but Excel has "optimized" the result and so corrects the answer to be
0.(?)
What I don't get is why the second example which only has the extra
operation of - 0 at the end, doesn't also get "optimized/corrected"?


"Ginger" wrote:

Wow - thanks JoeU, Jerry and Alan for the help!!
Honestly, I'll have to re-read your answers in order to (better)
understand
them, but you have really made my day to know that there is logic behind
what
happened!
As I stated, I had planned to override the formula in this row and move
on,
but just needed to know there was a reason for it acting so strangely.
I noticed the problem in the first place, because in col. I, I have an IF
statement: IF(H1=0,"COMPLETE","still active"). So, knowing this project
was
complete, and already billed in full, I didn't know why col. I was saying
"Still active".
That's when I expanded the decimals to see that it was indeed not 0.
I will try your options for fixing the formula, and re-post to tell you
how
it worked.

Simple subtraction formula returning strange results = Excel g
 

The characterization 'if the result of the last operation is "close" to zero'
may be misleading. Consider the following experiment:

In A1, place the formula =1+2^-50
In A3, place the formula =A1*A2-A2
In A4, place the formula =(A1*A2-A2)
In A5 place the formula =A1*A2&""
In A6 place the formula =A2&""

A4 will be nonzero, as it should be
If A2 contains any of 0.0000001, 1, 1E+25, 1E+100, 1E+300, 1E+307, then A3
will be zero even though the difference ranges from 9E-23 to 9E+291. Most of
these results are hardly "small". The issue seems to be that the two numbers
involved in the final subtraction are identical to 15 significant figures.

Even this characterization is not perfect, since 1000000 in A2 will give a
non-zero A3 even though A5 and A6 are still identical.

Jerry

"JoeU2004" wrote:

....
The following is my best explanation, deduced from experiments:

If the last operation is addition or subtraction that results in a change
from the intermediate result of the previous operations, and if the result
of the last operation is "close" to zero, Excel will return exactly zero.
Otherwise, Excel will return the exact result of the operations. Note that
a closing parenthesis (")") is considered an operation in this context.
(Sigh.)

Even that explanation is flawed insofar as it does not define what "close"
is. That's MS's fault.
....

Simple subtraction formula returning strange results = Excel g
 

"Jerry W. Lewis" wrote:
The characterization 'if the result of the last operation is
"close" to zero' may be misleading.

Agreed. I was merely expanding on the explanation from
http://support.microsoft.com/kb/78113, which states (emphasis added):
"Should an addition or subtraction operation result in a value at or very
__close_to_zero__, Excel 97 and later will compensate".

I should have known better than to think that MS knows what its product does
;-). But I did not want to stray too far from MS's explanation, since I
have no direct knowledge of the specification and implementation of this
heuristic.


The issue seems to be that the two numbers involved in the
final subtraction are identical to 15 significant figures.

I think you are close (no pun intended). But I'm not sure it has to do with
significant digits per se.

You offered one counter-example where the two operands of the last
subtraction are the same to 15 sig digits, yet Excel's heuristic does not
apply.

Consider the following counter-example where the two operands differ in the
first 15 sig digits (format as Scientific with 14 dp), yet Excel's heuristic
does apply.

A1: =2^1023+(2^1023-2^971) ''largest integer
A2: =2^1023+(2^1023-5*2^971) ''nearest integer that differs in first15 sig
digits (displayed)
A3: =A1-A2
A4: =(A1-A2)

A3 is exactly zero. A4 is about 7.98336123813888E+292.

A1 displays as 1.79769313486232E+308, but the first 30 digits of its exact
representation are 179769313486231,57081452742373 (the comma demarcates 15
sig digits to the left). A2 displays as 1.79769313486231E+308, but the
first 30 digits of its exact representation are
179769313486231,49098091504234.

Noting that the first 15 sig digits of the exact representations are the
same, it might be tempting to restate your rule in that way. However, here
is a counter-example to the restated rule.

A1, A3 and A4 are the same formulas as above.

A2: =2^1023+(2^1023-9*2^971) ''nearest integer to A1 that defeats Excel's
heuristic

A3 and A4 have the same internal representation, which is displayed as about
1.59667224762778E+293.

A2 displays 1.79769313486231E+308, the same as before. The first 30 digits
of its exact representation are 179769313486231,41114730266095. A1 and A2
have the same truncated 15 sig digits, like the first example; but A3 is not
zero, unlike the first example.

I have been looking at the binary representations, hoping to find a
consistent rule that might, in part, explain exceptions to your 15-sig-digit
rule. I have not been successful. The simplest observation might be: in
my second counter-example and in your counter-example with 1E+6, the
mantissa of the actual result is zero. That is, the difference is an exact
power of two. But the same can be said of my first counter-example.

The second-simplest speculation is that the operands of the last subtraction
differ only in some number of the least significant bits of the binary
representation. That has not panned out either. I've toyed with a number
of more complex bit-twiddling, to no avail. I'm giving up.

Much ado about nothing. Although reverse-engineering is always fun, it
should not matter if people do the right thing anyway, using ROUND
prolifically or setting "Precision as displayed".


----- original message -----

"Jerry W. Lewis" wrote in message
...
The characterization 'if the result of the last operation is "close" to
zero'
may be misleading. Consider the following experiment:

In A1, place the formula =1+2^-50
In A3, place the formula =A1*A2-A2
In A4, place the formula =(A1*A2-A2)
In A5 place the formula =A1*A2&""
In A6 place the formula =A2&""

A4 will be nonzero, as it should be
If A2 contains any of 0.0000001, 1, 1E+25, 1E+100, 1E+300, 1E+307, then A3
will be zero even though the difference ranges from 9E-23 to 9E+291. Most
of
these results are hardly "small". The issue seems to be that the two
numbers
involved in the final subtraction are identical to 15 significant figures.

Even this characterization is not perfect, since 1000000 in A2 will give a
non-zero A3 even though A5 and A6 are still identical.

Jerry

"JoeU2004" wrote:

...
The following is my best explanation, deduced from experiments:

If the last operation is addition or subtraction that results in a change
from the intermediate result of the previous operations, and if the
result
of the last operation is "close" to zero, Excel will return exactly zero.
Otherwise, Excel will return the exact result of the operations. Note
that
a closing parenthesis (")") is considered an operation in this context.
(Sigh.)

Even that explanation is flawed insofar as it does not define what
"close"
is. That's MS's fault.
...

Simple subtraction formula returning strange results = Excel g
 

Previously, I wrote:
The second-simplest speculation is that the operands of the last
subtraction differ only in some number of the least significant bits
of the binary representation.

Aha! Is it really that simple?

Excel replaces the exact result with zero when the difference between the
operands of the last subtraction [1], each taken as a 64-bit integer, is of
the form +/- n*2^x, where n is 1 to 7 and 2^x represents the
least-significant bit of the mantissa.

(No wonder the MS KB writer could not describe this correctly. I don't
think I can explain it any differently myself.)

Consider each of the examples we have discussed in this thread, starting
with Jerry's. The 64-bit floating-point representation is shown in the
stylized hex form &hEEEMMMMM,M...M, where "E" is the biased exponent and "M"
is the mantissa.


1. A1: =1+2^-50
A2: 1E-7 &h3E7AD7F2,9ABCAF48
A3: =A5-A2
A4: =(A5-A2)
A5: =A1*A2 &h3E7AD7F2,9ABCAF4F

Note that A5 could be written as: =A2+7*2^-76. Replacing 7 with 1 to 6
always results in a zero in A3. But A2+8*2^76 produces the same non-zero
result in A3 and A4. In all cases, A5 displays as 1.00000000000000E-07.

FYI, I do not see a change in the displayed value in A5 until
A2+39*2^-76. So I think the limit of 1 to 7 for "n" is arbitrary or
someone's idea of a compromise. (See example #7 below.)


2. Same as #1, but:
A2: 1E25 &h45208B2A,2C280291
A5: =A1*A2 &h45208B2A,2C280295

A5 is: =A2+4*2^31. Replacing 4 with 1 to 7 always results in a zero in
A3. But 8 produces the same non-zero result in A3 and A4. In all cases, A5
displays as 1.00000000000000E+25.


3. Same as #1, but:
A2: 1E100 &h54B249AD,2594C37D
A5: =A1*A2 &h54B249AD,2594C382

A5 is: =A2+5*2^280. Replacing 5 with 1 to 7 always results in a zero
in A3. But 8 produces the same non-zero result in A3 and A4. In all
cases, A5 displays as 1.00000000000000E+100.


4. Same as #1, but:
A2: 1E300 &h7E37E43C,8800759C
A5: =A1*A2 &h7E37E43C,880075A2

A5 is: =A2+6*2^944. Replacing 6 with 1 to 7 always results in a zero
in A3. But 8 produces the same non-zero result in A3 and A4. In all cases,
A5 displays as 1.00000000000000E+300.


5. Same as #1, but:
A2: 1E307 &h7FAC7B1F,3CAC7433
A5: =A1*A2 &h7FAC7B1F,3CAC743A

A5 is: =A2+7*2^967. Replacing 7 with 1 to 6 always results in a zero
in A3. But 8 produces the same non-zero result in A3 and A4. In all cases,
A5 displays as 1.00000000000000E+307.


6. Same as #1, but:
A2: 1E6 &h412E8480,00000000
A5: =A1*A2 &h412E8480,00000008

A5 is: =A2+8*2^-33. Because "n" is 8, A3 and A4 have the same non-zero
result. Replacing 8 with 1 to 7 always results in a zero in A3. In all
cases, A5 displays as 1.00000000000000E+06.


7. B2: =2^1023+(2^1023-2^971) &h7FEFFFFF,FFFFFFFF
B3: =2^1023+(2^1023-5*2^971) &h7FEFFFFF,FFFFFFFB
B4: =B3-B2
B5: =(B3-B2)

B3 is: =B2-4*2^971. Replacing 4 to 1 to 7 always results in a zero in
B4. But 8 produces the same non-zero result in B4 and B5. When "n" is 1 to
3, B3 displays as 1.79769313486232E+308, the same as B2. When "n" is 4 to
8, B3 displays as 1.79769313486231E+308.

FYI, B2-8*2^971 is 2^1023+(2^1023-9*2^971), my second example in my
previous posting.


8. Same as #7 but (one of Ginger's first example):
B2: 14369.10 &h40CC108C,CCCCCCCD
B3: =(39795-13530.90-11895) &h40CC108C,CCCCCCCC

B2 is =B3+1*2^-39, and B4 is zero. Replacing 1 with 2 to 7 always
results in zero in B4. But 8 produces the same non-zero result in B4 and
B5. In all cases, B2 displays as 1.43691000000000E+04, the same as B3.

When Ginger effectively wrote 39795 - 13530.90 - 11895 - 14369.10 - 0
by including cells with zero, the two operands of the last subtraction are
&h40CC108C,CCCCCCCD and 0. Obviously, "n" is not 1 to 7, so B4 and B5 are
the same non-zero result.

Similarly with Ginger's second set of examples, namely 0.5 - 0.4 - 0.1
v. 0.5 - 0.4 - 0.1 - 0.


I am fairly confident of my conclusion. But when I get a chance, I might
test with randomly generated "close" operands.


-----
Endnotes:

[1] MS says "addition or subtraction". For the former, I presume they mean
the addition of operands of opposite signs, which is just another form of
subtraction.


------ original message -----

"JoeU2004" wrote in message
...
"Jerry W. Lewis" wrote:
The characterization 'if the result of the last operation is
"close" to zero' may be misleading.

Agreed. I was merely expanding on the explanation from
http://support.microsoft.com/kb/78113, which states (emphasis added):
"Should an addition or subtraction operation result in a value at or very
__close_to_zero__, Excel 97 and later will compensate".

I should have known better than to think that MS knows what its product
does ;-). But I did not want to stray too far from MS's explanation,
since I have no direct knowledge of the specification and implementation
of this heuristic.


The issue seems to be that the two numbers involved in the
final subtraction are identical to 15 significant figures.

I think you are close (no pun intended). But I'm not sure it has to do
with significant digits per se.

You offered one counter-example where the two operands of the last
subtraction are the same to 15 sig digits, yet Excel's heuristic does not
apply.

Consider the following counter-example where the two operands differ in
the first 15 sig digits (format as Scientific with 14 dp), yet Excel's
heuristic does apply.

A1: =2^1023+(2^1023-2^971) ''largest integer
A2: =2^1023+(2^1023-5*2^971) ''nearest integer that differs in first15
sig digits (displayed)
A3: =A1-A2
A4: =(A1-A2)

A3 is exactly zero. A4 is about 7.98336123813888E+292.

A1 displays as 1.79769313486232E+308, but the first 30 digits of its exact
representation are 179769313486231,57081452742373 (the comma demarcates 15
sig digits to the left). A2 displays as 1.79769313486231E+308, but the
first 30 digits of its exact representation are
179769313486231,49098091504234.

Noting that the first 15 sig digits of the exact representations are the
same, it might be tempting to restate your rule in that way. However,
here is a counter-example to the restated rule.

A1, A3 and A4 are the same formulas as above.

A2: =2^1023+(2^1023-9*2^971) ''nearest integer to A1 that defeats
Excel's heuristic

A3 and A4 have the same internal representation, which is displayed as
about 1.59667224762778E+293.

A2 displays 1.79769313486231E+308, the same as before. The first 30
digits of its exact representation are 179769313486231,41114730266095. A1
and A2 have the same truncated 15 sig digits, like the first example; but
A3 is not zero, unlike the first example.

I have been looking at the binary representations, hoping to find a
consistent rule that might, in part, explain exceptions to your
15-sig-digit rule. I have not been successful. The simplest observation
might be: in my second counter-example and in your counter-example with
1E+6, the mantissa of the actual result is zero. That is, the difference
is an exact power of two. But the same can be said of my first
counter-example.

The second-simplest speculation is that the operands of the last
subtraction differ only in some number of the least significant bits of
the binary representation. That has not panned out either. I've toyed
with a number of more complex bit-twiddling, to no avail. I'm giving up.

Much ado about nothing. Although reverse-engineering is always fun, it
should not matter if people do the right thing anyway, using ROUND
prolifically or setting "Precision as displayed".


----- original message -----

"Jerry W. Lewis" wrote in message
...
The characterization 'if the result of the last operation is "close" to
zero'
may be misleading. Consider the following experiment:

In A1, place the formula =1+2^-50
In A3, place the formula =A1*A2-A2
In A4, place the formula =(A1*A2-A2)
In A5 place the formula =A1*A2&""
In A6 place the formula =A2&""

A4 will be nonzero, as it should be
If A2 contains any of 0.0000001, 1, 1E+25, 1E+100, 1E+300, 1E+307, then
A3
will be zero even though the difference ranges from 9E-23 to 9E+291.
Most of
these results are hardly "small". The issue seems to be that the two
numbers
involved in the final subtraction are identical to 15 significant
figures.

Even this characterization is not perfect, since 1000000 in A2 will give
a
non-zero A3 even though A5 and A6 are still identical.

Jerry

"JoeU2004" wrote:

...
The following is my best explanation, deduced from experiments:

If the last operation is addition or subtraction that results in a
change
from the intermediate result of the previous operations, and if the
result
of the last operation is "close" to zero, Excel will return exactly
zero.
Otherwise, Excel will return the exact result of the operations. Note
that
a closing parenthesis (")") is considered an operation in this context.
(Sigh.)

Even that explanation is flawed insofar as it does not define what
"close"
is. That's MS's fault.
...