Will excel generate one-tailed confidence intervals for Poisson distribution?

[Dora Smith]

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.

 

[Mike Middleton]

Dora -

... 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? <

I think that's what the POISSON worksheet function does when you set the
third argument (cumulative) equal to TRUE.

- Mike
http://www.mikemiddleton.com

 

[Jerry W. Lewis]

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

 

[Mike Middleton]

Dora -

Disregard my reply.

It seems you started a new thread instead of continuing your original
thread, and I didn't realize you are working with an extreme situation where
there may be problems using Excel's built-in functions.

- Mike

 

[Dora Smith]

No, when you do that, it computes the probability for the value that you
specify. Or else up to the value you specify. TRUE does one and FALSE does
the other.

I want to be able to determine what value corresponds to a given
probability.

 

[Dora Smith]

No problem.

 

[Dora Smith]

Well, my question is, does Excel have a way to do it or not?

(If he has to buy a new program, the idea might disappear.)

 

[Dora Smith]

Thanks - but in addition to this, I specifically need to be able to run a
one tail confidence interval with the poisson distribution. In other
words, I need to compute what quantity I have a 99% probability of
achieving.

That is different from computing what probability I have of achieving a
particular number.

 

[Dora Smith]

I can't find in the Smith site anywhere instructions on how to use them.
Do I copy and paste something? Insert values into the worksheet?

 

[Mike Middleton]

Dora -

The functions are available when the examples.xls worksheet is open.

You enter the function in a worksheet cell.

I think you may want to use Smith's critpoiss function. For example, if you
enter =critpoiss(7,0.99) into a worksheet cell, where 7 is the mean of the
Poisson distribution and 0.99 is the cumulative probability, the function
returns the value 14.

As I wrote earlier, I have lost track of your original thread, so I don't
know the relevant values for your situation.

To view the VBA code for the functions, press Alt-F11 and open Module1.

Also, the terminology "confidence interval" usually refers to a range based
on analyzing a random sample. I think what you desire is usually called a
critical value, e.g., a value such that the cumulative probability is less
than a specified probability.

- Mike
http://www.mikemiddleton.com

 

[Dora Smith]

Well, my question now is, where is the discussion that tells exactly what
Smith's workbook consists of and how to use it? It's pretty useless if you
just get confronted with this sheet full of God knows what! Y'all have
sent me to this page, but no clue what they do!

 

[Jerry W. Lewis]

Smith's workook contains functions for calculating probability related
quantities using algorithms that AFAIK are as accurate or more accurate than
any double precision implementation available anywhere (including commercial
statistics packages and commercial math libraries). In addition to the VBA
code that defines these functions, the workbook contains one worksheet that
defines what functions are available (B3:B15), gives example of the use of
each function (A19:F241), and gives a web link for more information E1.

Rather than being a tutorial, the presentation does assume that the user has
a minimum understanding of the types of functions outlined in B3:B15.

For the Poisson distribution,
pmf_poisson(mean,x) corresponds to Excel's POISSON(x,mean,FALSE) which is
documented in HELP.
cdf_poisson(mean,x) corresponds to Excel's POISSON(x,mean,TRUE)
comp_cdf_poisson(mean,x) corresponds to 1-POISSON(x,mean,TRUE)
crit_poisson(mean,p) is analogous to Excel's CRITBINOM function, but for
the Poisson distribution instead of the Binomial distribution
lcb_poisson(x,0.05) is a 1-tailed 95% lower confidence bound for the
Poisson mean based on observing a Poisson count of x
ucb_poisson(x,0.05) is a 1-tailed 95% upper confidence bound for the
Poisson mean based on observing a Poisson count of x

Perhaps your experience has been different, but I have generally found it
counterproductive to blame those I was seeking help from for my lack of
understanding, instead of simply asking for clarification.

Jerry

 

[Dora Smith]

Thanks!

Now, how do I insert VBA code in an Excel worksheet?

 

[Dora Smith]

No, actually the Excel POISSON function gives you the probability of getting
a particular value. I have the probability, want the value. I want to
know what values one has a 99% probability of getting.

 

[Dora Smith]

So you just enter the function as written in VBA into the cell?

 

[Dora Smith]

Umm, Jerry, I expect people to tell me how to use things I obviously don't
know how to use, if they are going to tell me to use them.

What is supposed to be the earthly point of telling someone to use something
and not tell her how to use it? Are you trying to help me, or merely show
off your knowledge and play up how much I don't know?

No - don't answer that. There are some people on this newsgroup who are
helpful, and others whose experience or whatever is truly unique. Maybe
it's called academic statistics; I don't know - people in that department
sure were a prize when I went to school! And yours isn't even the best of
the exasperating answers! Sorry. I know you wanted the prize for
creative rudeness. I don't still go to that school, and I'm not in
academia, and you aren't going to intelligibly answer my questions no matter
what I do or don't do, so I've no particular reason to be patient with your
rudeness. But you were too arrogant to think of all that.

 

[iandjmsmith]

So you just enter the function as written in VBA into the cell?

--
Yours,
Dora Smith
Austin, TX













- Show quoted text -

Just a quick aside to explain why Dora is very frustrated with all
this...

Dora wrote to me about the problem on Feb 11. I replied and in the
course of the reply mentioned if she did not know how to incorporate
VBA code into a spreadsheet the to ask again. She duly did. I replied.
The reply is still flying around in the ether somewhere.

She has now sent me an e-mail saying she needs a reply at which point
I've seen these messages and can understand why she thinks no-one is
helping.

Anyway Dora, apologies from me and AOL.

And in case my e-mails still don't get through...



If you open up an excel spreadsheet and press ALT-F11 it will take you
to a window for VBA tasks. Select Insert/Module from the menu and
paste in the text from Examples.txt. You can then close the VBA window
and the functions should be available to you in your own spreadsheet.

For example, =lcb_poisson(1000000,0.95) should return 1001645.422

The full list of functions available can be displayed if you select
Insert/Function from the Excel menu to bring up the Paste function
dialogue box and the select User Defined from the Function Category.



I gather in an earlier message you want to find the value P such that
=POISSON(P,mean,TRUE) returns 0.99

This value is returned by =crit_poisson(mean,0.99) or
=comp_crit_poisson(mean,0.01). Use of the comp_crit_poisson function
allows you to solve for extreme values such as 0.99999999999999999999.
0.99999999999999999999 cannot be accurately distinguished from 1 and
Normal approximations may no longer be accurate.

=comp_crit_poisson(617000,0.01) returns 618828. Note
cdf_poisson(617000, 618827) returns a value less than 0.99 and
cdf_poisson(617000, 618828) returns a value greater than or equal to
0.99.

=comp_crit_poisson(617000,0.00000000000000000001) returns 624290.
cdf_poisson(617000, 624289) & cdf_poisson(617000, 624290) both just
return 1 but comp_cdf_poisson(617000, 624289) returns just over 1e-20
and comp_cdf_poisson(617000, 624290) returns just under 1e-20.


Ian Smith

Finally a request from me. Once you have sorted out the calculations
you wanted in the first place (please ask more questions if I got it
wrong above), could you get back to me with the information you think
would have made your life easier in the first place. I will then make
sure the information can be accessed via the Examples spreadsheet.

 

[Jerry W. Lewis]

Unfortunately, 12 minutes between your previous post and this one is not a
new record for impatience. The people who provide information in these
newsgroups are not paid and do have lives, so it is quite common for there to
be a lag between when you post a question and when a particular person may
read and respond to that question. Another aspect of us not being paid, is
that in effect you are asking us to do you a favor. Is this how you ask for
favors in the real world?

I'm sorry that you are having so much trouble figuring out how to use the
Smith library; most people don't.

Also, forgive me if I have over-estimated your level of understanding, but
if you don’t ask specific questions, we have to make assumptions.

The simplest way to use the Smith library is to add worksheets to
Examples.xls, which you downloaded from Ian Smith's web page. The code is
already in that workbook, so you can simply use it on worksheets that you add
to that workbook.

If adding your workbooks into Examples.xls is not appropriate, you can use
Alt+F11 to start the VBA Editor. In the Project window, under Examples.xls
you will find a folder marked “Modulesâ€. In that folder is a single module
page that contains all the VBA code for Smith’s functions. For the workbook
that you want to add the code to, you can Insert|Module from the VBA Editor
menu, then copy the code from the Examples.xls module into the one you added
to the new workbook.

The resulting calculations would be slightly less accurate than in
Examples.xls, due to the MS decision to display no more than 15 figures on
floating point numbers. You can achieve the same precision as in
Examples.xls by pasting the code into a text editor. In that text editor,
wherever there is a code comment (preceded by a single quote) that contains a
numeric value to more figures than is shown in the actual code, then
copy/paste the commented value over the code value. When finished, copy all
the code from the text editor into the module that you added to the new
workbook. This is a lot of work and presumes a certain level of
understanding; hence my suggestion that the easiest approach is to add
worksheets to Examples.xls.

Jerry