Mister_T said:
AAARRRRGGGHHHHH!!!!! What is going on. I don't see how this can be
anything
other than a bug. I have searched the MS site to look for a way of
reporting
a bug but there doesn't seem to be one. Anyone got any ideas on this
probelm
or know how to report a bug
No, this is not a defect. It is a side-effect of the way that Excel (and
most applications) do arithmetic on binary computers. But before I get
into my explanation, here some pointers to Microsoft's:
http://support.microsoft.com/kb/78113
http://support.microsoft.com/kb/42980
http://support.microsoft.com/kb/214118
(58,511.55 + 86.66 - <empty cell>)
[The 58k value is itself a result of another formula like the above]
You would expect 58,598.21 but instead excel gives 58,598.210000000100000
Your parenthetical comment (58511.55 is the result of a formula) is the
key. In this particular case, we cannot duplicate your results simply by
typing (58511.55 + 86.66 - 0), with or without the parentheses, which
surprisingly can make a difference sometimes.
In your case, the expression does not equal 58598.21 "exactly" (i.e.
within 15 significant digits) because 58.511.55 and/or 86.66 are not
"exactly" those values within 15 significant digits. Format each cell as
Scientific with 14 decimal places, and you should see the disparity.
(If you are unfamiliar with Scientific notation, don't worry. It is just
a consistent way of seeing 15 significant digits regardless of the
magnitude of the number.)
What you probably really want to know is: how do you avoid this very
common anomaly?
The answer is: use ROUND judicious and, IMHO, prolifically. For example,
ROUND(A1+A2-A3,2), where A1 has the formula that results in 58511.55, A2
has the formula that results in 86.66, and A3 is the empty cell.
(Note: Another alternative is to set the "Precision as displayed" option
under Tools > Options > Calcuation. I do not recommend it for some very
specific reasons. If you are interested, post a response in this thread,
and I will explain.)
Since I cannot duplicate your example, lacking the exact values, I'll use
my favorite examples to explain.
Consider 10.1 - 10 in A1 and 0.1 in A2. =(A1=A2) is FALSE, and =A1-A2
is -3.608E-16.
In a nutshell, this is because numbers are represented internally by 53
consecutive powers of 2 ("bits"). With 0.1, we can use the full 53 bits
to represent 0.1. But with 10.1, some of the bits are used to represent
10; so there are fewer bits to represent 0.1. In this case, that results
in a different representation of 0.1 as part of 10.1. When we subtract
10, we are left with this different representation.
This might be clearer if you could see the exact decimal representation of
the internal values. Excel will not do that; it limits itself to
presenting only the first 15 significant digits. But the exact internal
value of 10.1 is 10.0999999999999,996447286321199499070644378662109375;
the exact internal value of 10.1 - 0.1 is
0.0999999999999996,447286321199499070644378662109375; and the exact
internal value of 0.1 is
0.100000000000000,0055511151231257827021181583404541015625. (The comma is
my way of demarcating the first 15 significant digits to the left.)
The exact internal values of 10.1 - 0.1 and 0.1 are not close enough for
Excel to consider them equal. Note the words "close enough". Excel has
implemented some heuristics to consider two different internal values as
equal under some very narrow conditions. Unfortunately, those heuristics
often add to the confusion, in part because they are poorly defined, IMHO.
For example, consider 0.1 + 2^-56 in A1 (just slightly larger than 0.1)
and 0.1 in A2. =(A1=A2) returns TRUE even though the internal values
obviously are not equal. Likewise, =A1-A2 returns zero. But =(A1-A2)
returns non-zero, namely about 1.39E-17; note that the only difference is
the parentheses. And for the same reason, =IF(A1-A2=0,TRUE) returns
FALSE, which flies in the face of reason considering the result of =A1-A2.
The latter example is explained somewhat if you read between the lines
under the heading "Example When a Value Reaches Zero" on the web page at
http://support.microsoft.com/kb/78113 . The explanation is flawed(!); but
it is the best that Microsoft has to offer. You would not like my
more-precise explanation, which I reverse-engineered. It is really too
techy. (But if you really want to know, ask for it in a response in this
thread.)
Hope this helps. I would be happy to go into more detail if you have
questions. It might be helpful if you posted the Scientific format (with
14 dp) of the numbers in question. But bear in mind that even that is
sometimes not good enough for us to see the difference; consider my
example of 0.1 + 2^-56.
At the very least, I hope the "short" explanation above demonstrates that
this is not a defect per se.
----- original message -----
Mister_T said:
I have the following formula in excel:
=IF((F157+G157)>0,IF(B157<>"#",(L156+G157-F157),L156),0)
The first two IFs evaluate to true so the result is basicaly
L156+G157-F157
and the values are
(58,511.55 + 86.66 - <empty cell>) [The 58k value is itself a result of
another formula like the above]
You would expect 58,598.21 but instead excel gives 58,598.210000000100000
Setting up three additional formulas that are just an equals on the two
data
cells and the result gives the following:
58,511.55000000000000000
86.6600000000000000000
58,598.210000000100000000
Doing an 'Evaluate Formula' gives
IF(TRUE,IF(TRUE,(58511.55+86.66-F157),L156),0) and then
IF(TRUE,IF(TRUE,(58,598.2100000001-F157),L156),0)
AAARRRRGGGHHHHH!!!!! What is going on. I don't see how this can be
anything
other than a bug. I have searched the MS site to look for a way of
reporting
a bug but there doesn't seem to be one. Anyone got any ideas on this
probelm
or know how to report a bug
Changing the 86.66 to .65 or .67 removes the 1 at the 10th decimal place.
I
can work around it by using two rows in my sheet to make up the 86.66
transaction but if this keeps happening it is going to be very annoying.
