"Mister_T" wrote:
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,996447286321199499070644378662109 375; the exact internal
value of 10.1 - 0.1 is 0.0999999999999996,4472863211994990706443786621093 75;
and the exact internal value of 0.1 is
0.100000000000000,00555111512312578270211815834045 41015625. (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" wrote in message
...
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.