Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Confidence Intervals | Excel Discussion (Misc queries) | |||
Multiple Excel versions. | Excel Discussion (Misc queries) | |||
Generate 1-to-1 outputs for a list of inputs into a excel formula | Excel Discussion (Misc queries) | |||
Statistical Excel Function Question within Excel 2000... | Excel Worksheet Functions |