Henry said:
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 n>17 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 -----
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.
