Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default Will excel generate one-tailed confidence intervals for Poisson distribution?

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



  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 762
Default Will excel generate one-tailed confidence intervals for Poisson distribution?

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

"Dora Smith" wrote in message
...
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




  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default Will excel generate one-tailed confidence intervals for Poisson di

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




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 762
Default Will excel generate one-tailed confidence intervals for Poisson distribution?

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

"Mike Middleton" wrote in message
...
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

"Dora Smith" wrote in message
...
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






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default Will excel generate one-tailed confidence intervals for Poisson distribution?

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.

--
Yours,
Dora Smith
Austin, TX

"Mike Middleton" wrote in message
...
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

"Dora Smith" wrote in message
...
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








  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default Will excel generate one-tailed confidence intervals for Poisson distribution?

No problem.

--
Yours,
Dora Smith
Austin, TX

"Mike Middleton" wrote in message
...
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

"Mike Middleton" wrote in message
...
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

"Dora Smith" wrote in message
...
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








  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default Will excel generate one-tailed confidence intervals for Poisson distribution?

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

--
Yours,
Dora Smith
Austin, TX

"Mike Middleton" wrote in message
...
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

"Mike Middleton" wrote in message
...
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

"Dora Smith" wrote in message
...
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








  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default Will excel generate one-tailed confidence intervals for Poisson di

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.

--
Yours,
Dora Smith
Austin, TX

"Jerry W. Lewis" wrote in message
...
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






  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default Will excel generate one-tailed confidence intervals for Poisson di

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?

--
Yours,
Dora Smith
Austin, TX

"Jerry W. Lewis" wrote in message
...
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






  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 762
Default Will excel generate one-tailed confidence intervals for Poisson di

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" wrote in message
...
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?

--
Yours,
Dora Smith
Austin, TX

"Jerry W. Lewis" wrote in message
...
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










  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default Will excel generate one-tailed confidence intervals for Poisson di

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!

--
Yours,
Dora Smith
Austin, TX

"Mike Middleton" wrote in message
...
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" wrote in message
...
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?

--
Yours,
Dora Smith
Austin, TX

"Jerry W. Lewis" wrote in message
...
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










  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default Will excel generate one-tailed confidence intervals for Poisso

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" wrote:

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!

--
Yours,
Dora Smith
Austin, TX

"Mike Middleton" wrote in message
...
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" wrote in message
...
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?

--
Yours,
Dora Smith
Austin, TX

"Jerry W. Lewis" wrote in message
...
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











  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default Will excel generate one-tailed confidence intervals for Poisso

Thanks!

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

--
Yours,
Dora Smith
Austin, TX

"Jerry W. Lewis" wrote in message
...
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" wrote:

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!

--
Yours,
Dora Smith
Austin, TX

"Mike Middleton" wrote in message
...
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" wrote in message
...
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?

--
Yours,
Dora Smith
Austin, TX

"Jerry W. Lewis" wrote in message
...
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













  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default Will excel generate one-tailed confidence intervals for Poisson distribution?

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.

--
Yours,
Dora Smith
Austin, TX

"Mike Middleton" wrote in message
...
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

"Dora Smith" wrote in message
...
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






  #15   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default Will excel generate one-tailed confidence intervals for Poisson di

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

--
Yours,
Dora Smith
Austin, TX

"Mike Middleton" wrote in message
...
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" wrote in message
...
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?

--
Yours,
Dora Smith
Austin, TX

"Jerry W. Lewis" wrote in message
...
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












  #16   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default Will excel generate one-tailed confidence intervals for Poisso

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.

--
Yours,
Dora Smith
Austin, TX


"Jerry W. Lewis" wrote in message
...
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



  #17   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3
Default Will excel generate one-tailed confidence intervals for Poisson di

On 16 Feb, 01:52, "Dora Smith" wrote:
So you just enter the function as written in VBA into the cell?

--
Yours,
Dora Smith
Austin, TX
"Mike Middleton" wrote in message

...



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" wrote in message
.. .
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?


--
Yours,
Dora Smith
Austin, TX

"Jerry W. Lewis" wrote in message
...
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 thenormal 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
- Hide quoted text -


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


  #18   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 837
Default Will excel generate one-tailed confidence intervals for Poisso

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 dont 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 Smiths 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

"Dora Smith" wrote:

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.

--
Yours,
Dora Smith
Austin, TX


"Jerry W. Lewis" wrote in message
...
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
Confidence Intervals waldoafit Excel Discussion (Misc queries) 2 January 10th 07 08:36 PM
Multiple Excel versions. Naveen Mukkelli Excel Discussion (Misc queries) 0 May 16th 06 12:55 AM
Generate 1-to-1 outputs for a list of inputs into a excel formula JBollinger1234 Excel Discussion (Misc queries) 1 May 11th 06 01:02 PM
Statistical Excel Function Question within Excel 2000... Drew H Excel Worksheet Functions 3 October 31st 04 07:55 PM


All times are GMT +1. The time now is 09:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"