Re: Simple subtraction formula returning strange results = Excel glitc
Alan
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" <Gin...@discussions.microsoft.com> wrote in message
news:0D51AFFF-67D9-4CC5...@microsoft.com...
> 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 were: $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.
JoeU2004
> 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 were: $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 -----
Alan
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" <joeu...@hotmail.com> wrote in message
news:%23d$hdkOrJ...@TK2MSFTNGP03.phx.gbl...
JoeU2004
> 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" <ala...@ntlworld.com> wrote in message news:uyrx$qOrJH...@TK2MSFTNGP03.phx.gbl...
Jerry W. Lewis
=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/microsoft.public.excel/msg/6efb95785d1eaff5
Jerry
Ginger
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.
Jerry W. Lewis
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.40000000000000002220446049250313080847263336181640625
0.1000000000000000055511151231257827021181583404541015625
If you do the math, you will see that =0.5-0.4 (using the approximation to
0.4) is exactly
0.09999999999999997779553950749686919152736663818359375
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"?
JoeU2004
> 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" <Gin...@discussions.microsoft.com> wrote in message
news:A3E5C3A1-2ADB-4720...@microsoft.com...
> 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:
>
Jerry W. Lewis
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.
...
JoeU2004
> 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" <post_a_reply@no_e-mail.com> wrote in message
news:0C19C96E-62B0-4665...@microsoft.com...
JoeU2004
> 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" <joeu...@hotmail.com> wrote in message
news:ugT9tYrs...@TK2MSFTNGP03.phx.gbl...