Ian Smith's library of probability functions includes functions for Poisson
confidence intervals. You do not have to know VBA to use them, you just have
to be willing to use a workbook that has VBA code in it. You use them in
cell formulas, just like native Excel worksheet functions. The worksheet in
http://members.aol.com/iandjmsmith/Examples.xls
gives example of the use of each function.
If you do not want to use the normal approximation with your large numbers,
then there is no alternative in Excel to using the Smith library. Prior
Excel 2003, POISSON(,,TRUE) fails to return values well below a mean of 1000.
In Excel 2003 and Excel 2007, POISSON(,,TRUE) does return values for large
means, but they are not to be trusted! For instance POISSON(x,x,TRUE) should
converge toward 0.5 for large x, but instead it converges toward 1. The
Smith functions handle these calculations correctly, and
AFAIK are the best
double precision implementation available anywhere.
If the presence of VBA under the hood is a show stopper, then consider
downloading an using Stephen Bye's Excel compatible spreadsheet Spread32
http://www.byedesign.freeserve.co.uk/
which has a far more accurate POISSON function (with a larger working range)
than either Excel 2003 or 2007.
Jerry
"Dora Smith" wrote:
OK, I understand why it makes more sense to use the normal distribution, on
our hundreds of thousands of records, but my boss is not convinced - mabye
because he knows the Poisson distribution is skewed to the left, and he said
something about getting higher probabilities than 97%. I dunno. Maybe
the purpose has to do with improving performance.
My question now is, can Excel generate one-tailed confidence intervals for
the Poisson distribution? In other words, is there a way to plug a
level of probability you want to achieve in and have it give you the number
of records one has a 99% probability of achieving?
If not, what plugins for Excel are available to accomplish that - without
needing to know visual basic or something to use them?
I have an idea not, but I'm double checking.
--
Yours,
Dora Smith
Austin, TX