"someone" wrote:
"=COMBIN(9,3)-84-0" gives -1.42109E-14
yet if I reverse "-84-0" to be "-0-84" I get the
correct answer
"=COMBIN(9,3)-0-84" gives 0
Why is this so?

I already answered that question in this thread -- well, as well as it can
be, I think.

It is due to the half-baked heuristic described (poorly) in KB 78113. See
the section "Example When the Value Reaches Zero" at
support.microsoft.com/kb/78113.

Your observation is a common side-effect of the heuristic, which is why I
call it
half-baked.

In a nutshell, the heuristic applies only if the last operation is
subtraction (or addition of operands with opposite signs). The exact nature
of the heuristic is difficult to infer. It is even more difficult to
explain, especially to anyone who is not familiar with computer arithmetic
at the bit level. For grins, see my response at
http://groups.google.com/group/micro...caa51ae5025678 .
As I reread that today, I am not sure even that is a complete explanation.

Suffice it to say that the heuristic is intended to hide the small
aberrations that arise in IEEE 754 64-bit floating-point arithmetic. But it
is implemented so poorly that it creates more seemingly inexplicable
anomalies.

For example, =COMBIN(9,3)-84 results in exactly zero, but =(COMBIN(9,3)-84)
does not.

There simply is no good reason for such differences. It is simply a poor
implementation of a well-intentioned, albeit dubious heuristic.


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

"someone" wrote in message
...

"Henry" wrote in message
...
In theory COMBIN works with integers in Excel (2003 and 2007).

So "=COMBIN(9,3)" gives 84 and so does "=COMBIN(9.7,3.6)".
Similarly "=COMBIN(9,3)-84" gives 0.

But "=COMBIN(9,3)-84-0" gives -1.42109E-14
and there are many similar examples suggesting that some sort of
rounding is involved.
The sign can even change so for example "=COMBIN(15,3)-455-0" gives
5.68434E-14

=IF(COMBIN(9,3)=84,"same","different") and
=IF(COMBIN(9,3)-84=0,"same","different") do not give identical
results.
Nor do =COMBIN(9,3)-67-17 and =COMBIN(9,3)-68-16.




It gets stranger:

As you have noted,
"=COMBIN(9,3)" gives 84
"=COMBIN(9,3)-84" gives 0
but
"=COMBIN(9,3)-84-0" gives -1.42109E-14
yet if I reverse "-84-0" to be "-0-84" I get the correct answer
"=COMBIN(9,3)-0-84" gives 0
Why is this so?
I use Excel 2003, Windows Vista Ultimate 32 bit
Brian (a novice)