"Henry" wrote:
On 29 Mar, 06:50, "Schizoid Man" wrote:
There are several ways to reproduce such errors in Excel.
E.g. NORMSINV(NORMSDIST(0)). Rather than the zero that
one would expect, one gets -1.39213763529183E-16.

Or even NORMSINV(1/2), But you don't usually expect NORMSINV
to return an integer for other values.

Or just about any expression involving numbers with decimal fractions.

(Although I would like an implementation of functions like NORMSINV to
recognize special cases and side-step the usual approximation method when an
exact value can be returned.)


you don't usually expect NORMSINV to return an integer
for other values. You do expect COMBIN to do so.

Yes, with the understanding that the ability to return an exact and accurate
integer depends, in part, on the magnitude of the values involved.

With the standard way that Excel represents numbers internally -- IEEE 754
64-bit floating point -- generally we can only be assured of representing
integers up to 15 digits accurately. (There are exceptions, as demonstrated
below.)

But the degree to which that impacts the result of COMBIN depends on its
internal implementation.

If COMBIN(n,k) were implemented internally effectively like
FACT(n)/FACT(n-k)/FACT(k), we might not expect exact and accurate integer
results for n17 because FACT(18) exceeds 15 digits.

(The operative word is "expect". It depends on other computational factors,
as well. As it turns out, FACT(n) is accurate for n<=20.)

However, a "good" implementation of COMBIN(n,k) might be implemented as
PRODUCT[x, for x=MAX(k,n-k)+1,...,n]/FACT(MIN(k,n-k)). That would greatly
extend the range of n for which we can expect exact integer results within
the computational limitations of 64-bit floating-point arithmetic.

Empirically, incrementing n starting from 1 and for k=1 to n, with the first
(factorial) method implemented in VBA, n=23 and k=2 is the first combination
that should result in a non-integer result. With the second ("good")
method, n=31 and k=14 is the first combination that should result in a
non-integer result.

(I am only checking to see if the result is an integer. I did not vet the
correctness of the integer results.)

PS: This is a digression. None of the above explains why COMBIN(9,3)
returns a non-integer. As I noted previously, even FACT(9)/FACT(6)/FACT(3)
is well within the computational limitations of 64-bit floating-point
arithmetic.


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

"Henry" wrote in message
...
On 29 Mar, 06:50, "Schizoid Man" wrote:
"joeu2004" wrote in message
"Henry" wrote:
Similarly "=COMBIN(9,3)-84" gives 0.
But "=COMBIN(9,3)-84-0" gives -1.42109E-14

Interesting find! What that tells us is thatCOMBIN(9,3) is not
returning an integer(!). Indeed,COMBIN(9,3) returns exactly
83.9999999999999,857891452847979962825775146484375 in IEEE 64-bit
floating point form.

There are several ways to reproduce such errors in Excel. E.g.
NORMSINV(NORMSDIST(0)). Rather than the zero that one would expect, one
gets -1.39213763529183E-16.

Or even NORMSINV(1/2), But you don't usually expect NORMSINV to return
an integer for other values. You do expect COMBIN to do so.