How do I point out an error in an Excel function?

[ncarron]

I'm a registered user of Excel 2007 (part of Office 2007).

I want to point out an error in an Excel function (the statistical Poisson distribution function), and request it be corrected with some update.

How do I do that?
When I try to contact technical support I'm asked what payment method I want to use.
Microsoft is making it very difficult to be a good Samaritan.

Thanks. Neal Carron

[shg]

What's the error, and a test case that demonstrates it?

[ncarron]

The Poisson distribution function in Excel 2007 (SP2) gives incorrect results for large means. For a mean of 10000, the correct probability of getting, say, 10000, is 0.003989, whereas the Excel function returns 0.004082.

The error gets worse for larger mean. At a mean of 30000 the correct probability of getting 29827 (1 std dev off mean) is 0.001401, whereas the Excel function returns 0.001599.

The error in the latter example is 14%; the error is not academic. The number of gamma counts from a radioactive source in a short time interval can easily be tens of thousands, and nuclear researchers may rely on Excel results (as is the case in a recent project of my own).

The error appears to be in the algorithm Excel uses. The algorithm in the Wikipedia article (under "Poisson Distribution") gives the correct result, as does Numerical Recipes, as do online Poisson calculators, and as does a careful Fortran code written by me. As it occurs in the probability density function, it likely occurs in the cumulative distribution as well.

Can this be corrected with an update patch?

[shg]

I confirmed your results and have filed a bug report, Neal.

The array formula I used, which agrees with online calculators and your results, is

=EXP(x * LN(mean) - mean - SUM(LN(ROW(INDIRECT("1:" & x)))))