confintxls - Interactive Statistical Calculation Pages by runout

VIEWS: 9 PAGES: 6

									This spreadsheet contains six user-defined functions (written in MS Visual Basic for Applications) that make it
easy to compute event probabilities and exact confidence intervals for the Binomial and Poisson distributions.

When you open this file, Excel may warn you that it contains macros, which could contain computer viruses. You
will need to click the "Enable Macros" button in order to use the confidence interval functions. I'm quite sure that
If you are no viruses hiding in the spreadsheet.
there don't get a warning message, and the macros do not seem to be working, go to the "Tools" menu, click
"Macro", then select "Security...". In the Security window that comes up, click the "Security Level" tab, then select
"Medium", then click OK. Then re-open the spreadsheet. When you receive a warning message about how some
macros can contain viruses, click "Enable Macros".

I've written six user-defined functions: there are three binomial-related functions and three Poisson-related
functions.
BinomLow returns the lower bound of the confidence interval. You provide X (the observed number of
"successes"), N (the number of trials), and pL (the lower "tail area" for the confidence level). For a 95%
symmetrical two-sided confidence interval, you should set pL = 0.025 . By using different values of pL and pH for
the BInomLow and BinomHigh functions, you can get asymmetrical or one-sided confidence intervals.
BinomHigh returns the upper bound of the confidence interval. You provide X (the observed number of
"successes"), N (the number of trials), and and pH (the upper "tail area" for the confidence level). For a 95%
symmetrical two-sided confidence interval, you should set pH = 0.025 . By using different values of pL and pH for
the BInomLow and BinomHigh functions, you can get asymmetrical or one-sided confidence intervals.
BinomP computes the individual terms of the binomial distribution between two limits. This function is used by
BinomLow and BinomHigh, and you might find it useful in its own right if you need to compute probabilities for
obtaining a specific range of "successes" in N trials from a binomial distribution with a population proportion p.
You provide N (the total number of trials), p (the population proportion), and X1 and X2 (the lower and upper
range of "successes"), and the function returns the probability of getting between X1 and X2 (inclusive)
PoisLow, PoisHigh, and PoisP are the corresponding functions for the Poisson distribution. For PoisLow and
PoisHigh, you provide the observed number of events instead of the total number of trials and the number of
successes. For PoisP, you provide the mean event rate and a range of observed events.
Note: In the version of this spreadsheet posted on my web site between October 12, 2004 and August 11, 2007,
these functions incorrectly handled situations where the number of successes equals zero or equals the number
of trials (for binomial) or where the observed number of events equals zero (for Poisson). In these situations, the
the routines placed the entire 0.05 "tail area" (for a 95% CI) into one tail, rather than being split up into an upper
0.025 and a lower 0.025. This was done in the mistaken belief that such a re-allocation could succeed in
narrowing the confidence interval while still preserving the mandatory 95%-or-more coverage property that an
"exact" confidence interval must have. The error in this approach was brought to my attention earlier this year by
Karl Schlag. The formulas have now been returned to their original form, which is consistent with the classical
"Clopper-Pearson" methodology. If you have used macros from the 10/12/2004 - 08/11/2007 time period, you
While Excel provides its own built-in functions for binomial and Poisson probabilities, BINOMDIST fails (returning
#NUM!) for N larger than about 500, and POISSON fails for N larger than about 140. In contrast, my functions
are designed to work with very large values of N -- I've tested them successfully with values above 10,000,000
(although it may take a while for the results to appear -- the exact algorithm involves a lot of calculations).
My functions also provide an easier (and, in my humble opinion, a more logically consistent) way to obtain
probabilities for ranges of X (using X1 and X2), as well as for single X values (by using the same value for X1 and
X2).
The Simple Examples worksheet shows you how the functions can be used, and lets you play around
with different values to see the results. "The use of confidence or fiducial limits illustrated in the case of the
Reference: CJ Clopper and ES Pearson,
binomial." Biometrika 26:404-413, 1934.

Let me know if you have any problems or questions about this file. Send e-mail to jcp12345@gmail.com
John C. Pezzullo
Kissimmee, Florida, USA
Note: As of August 23, 2005, I have included another worksheet, called "Another Approach", which calculates the
exact confidence intervals using formulas involving the inverse Chi-Square and Fisher F probability functions
(which are built-in as a standard part of Excel).
If you don't want to use my macros, but want to stick with standard Excel formulas, then this page is for you. But
be aware that Excel's CHIDIST and FDIST functions are not very robust for extreme values of the arguments, so

Note: As of April 16, 2009, I have included another worksheet, called "Binom Table", which was created by Prof.
Patrick J. Laycock, of the University of Manchester, who has generously made it freely available to the world.
To create a table, you simply enter the "denominator" N (number of trials) and the confidence level, and it will
generate a table that shows, for every value of "numerator" (number of successes) between 0 and N, the
confidence intervals around the observed numerator and around the calculated percentage of success.
The worksheet is currently set up to handle N between 1 and 100 (it has formulas in rows 9 through 108), but it
can easily be extended to handle larger N. Just "propagate" the formulas in the last row down into as many
additional rows as you need. To do this, first you have to "unprotect" the sheet. Go to the Tools menu, select
Protection, then select "Unprotect Sheet". Then select cells A108 through L108 by dragging the mouse through
that range of cells. The group of cells will be surrounded with a heavy outline rectangle, and the lower-right corner
of this outline rectangle will be a tiny black square. Drag that square down, and it will replicate the formulas into

Note: As of July 5, 2009, I have also made these macros available as an Excel "add-in" module, called confint.xla,
which can be downloaded from the StatPages.org web site. This macro contains the same six functions
(BinomLow, BinomHigh, BinomP, PoisLow, PoisHigh, and PoisP) as in this spreadsheet file. But by downloading
and "installing" the add-in (check your Excel Help file to see how to install add-in's), you will have these six

Note: As of July 5, 2009, these functions do very thorough error-checking on the parameters. If any parameters
have invalid values, the function will return a character string containing a short but specific error message.
Binomial Distribution…
            BinomP function for sampling from a Binomial distribution…
Input-->                                 N=       10       Total number of trials
Input-->                              prop =      0.4      Population proportion of successes
Input-->                                x1 =       5       lower number of successes
Input-->                                x2 =       9       upper number of successes
Output-->                             prob =   #NAME?      Probability of drawing between x1 and x2 successes in N trials

            BinomLow and BinomHigh functions for exact binomial confidence intervals…
Input-->                               x=        0       Observed number of successes
Input-->                               N=   1.20E+07 Total number of trials
Input-->                           p-low =       0       Lower tail probability of confidence interval
Input-->                          p-high =     0.01      Upper tail probability of confidence interval
Output-->                     Conf Level =    99.0%      Resulting confidence level: =1-(D12+D13)
Output-->                     Proportion =    0.0000     Observed population proportion: =D10/D11
Output-->                        Low CI =   #NAME?       Lower bound of confidence interval for the population proportio
Output-->                        High CI =  #NAME?       Upper bound of confidence interval for the population proportio


Poisson Distribution…
            PoisP function for sampling from a Poisson distribution…
Input-->                           Lambda =        5.3      Expected number of events (mean event rate)
Input-->                                  x1 =      2       Lower number of events
Input-->                                  x2 =      8       Upper number of events
Output-->                              prob =   #NAME?      Probability of observing between x1 and x2 events: =PoisP(D2

            PoisLow and PoisHigh functions for exact Poisson confidence intervals…
Input-->                                 N=        3       Number of observed events
Input-->                             p-low =     0.025     Lower tail probability of confidence interval
Input-->                            p-high =     0.025     Upper tail probability of confidence interval
Output-->                      Conf Level =      95.0%     Resulting confidence Level: =1-(D29+D30)
Output-->                          Low CI =    #NAME?      Lower bound of confidence interval for the mean event rate: =
Output-->                          High CI =   #NAME?      Upper bound of confidence interval for the mean event rate: =


Enter values into the blue cells of column D.
The answers will appear in the black cells of column D.
1 and x2 successes in N trials: =BinomP(D3,D4,D5,D6)




val for the population proportion: =BinomLow(D10,D11,D12)
val for the population proportion: =BinomHigh(D10,D11,D13)




an event rate)


 x1 and x2 events: =PoisP(D22,D23,D24)




val for the mean event rate: =PoisLow(D28,D29)
val for the mean event rate: =PoisHigh(D28,D30)
This page calculates exact Binomial and Poisson CI's, using the Fisher F and Chi-square functions


Binomial…
Obs x             0     input
Obs N     1.20E+07      input
Conf Lev       99%      input
Lo CL        0.0000     output
Hi CL        0.0000     output


Poisson…
Obs N               3   input
Conf Lev         95%    input
Lo CL          0.6187   output
Hi CL          8.7673   output


Enter values into the blue cells of column B.
The answers will appear in the black cells of column B.
Table of exact binomial confidence limits for 1<=N<=100
With thanks to Prof Patrick J. Laycock, Manchester University, pjlaycock@manchester.ac.uk

                  N=         100                                                 p-low      p-high
          Conf Level =       95%                                                 0.025      0.025

                                                                                          lower    upper      half
          lower bound upper bound lower bound on upper bound                   count as bound on bound on   interval
 Count      on count    on count    proportion   on proportion                percentage percent  percent     size
    0       #NAME?      #NAME?       #NAME?        #NAME?                           0    #NAME? #NAME?      #NAME?
    1       #NAME?      #NAME?       #NAME?        #NAME?                           1    #NAME? #NAME?      #NAME?
    2       #NAME?      #NAME?       #NAME?        #NAME?                           2    #NAME? #NAME?      #NAME?
    3       #NAME?      #NAME?       #NAME?        #NAME?                           3    #NAME? #NAME?      #NAME?
    4       #NAME?      #NAME?       #NAME?        #NAME?                           4    #NAME? #NAME?      #NAME?
    5       #NAME?      #NAME?       #NAME?        #NAME?                           5    #NAME? #NAME?      #NAME?
    6       #NAME?      #NAME?       #NAME?        #NAME?                           6    #NAME? #NAME?      #NAME?
    7       #NAME?      #NAME?       #NAME?        #NAME?                           7    #NAME? #NAME?      #NAME?
    8       #NAME?      #NAME?       #NAME?        #NAME?                           8    #NAME? #NAME?      #NAME?
    9       #NAME?      #NAME?       #NAME?        #NAME?                           9    #NAME? #NAME?      #NAME?
   10       #NAME?      #NAME?       #NAME?        #NAME?                          10    #NAME? #NAME?      #NAME?
   11       #NAME?      #NAME?       #NAME?        #NAME?                          11    #NAME? #NAME?      #NAME?
   12       #NAME?      #NAME?       #NAME?        #NAME?                          12    #NAME? #NAME?      #NAME?
   13       #NAME?      #NAME?       #NAME?        #NAME?                          13    #NAME? #NAME?      #NAME?
   14       #NAME?      #NAME?       #NAME?        #NAME?                          14    #NAME? #NAME?      #NAME?
   15       #NAME?      #NAME?       #NAME?        #NAME?                          15    #NAME? #NAME?      #NAME?
   16       #NAME?      #NAME?       #NAME?        #NAME?                          16    #NAME? #NAME?      #NAME?
   17       #NAME?      #NAME?       #NAME?        #NAME?                          17    #NAME? #NAME?      #NAME?
   18       #NAME?      #NAME?       #NAME?        #NAME?                          18    #NAME? #NAME?      #NAME?
   19       #NAME?      #NAME?       #NAME?        #NAME?                          19    #NAME? #NAME?      #NAME?
   20       #NAME?      #NAME?       #NAME?        #NAME?                          20    #NAME? #NAME?      #NAME?
   21       #NAME?      #NAME?       #NAME?        #NAME?                          21    #NAME? #NAME?      #NAME?
   22       #NAME?      #NAME?       #NAME?        #NAME?                          22    #NAME? #NAME?      #NAME?
   23       #NAME?      #NAME?       #NAME?        #NAME?                          23    #NAME? #NAME?      #NAME?
   24       #NAME?      #NAME?       #NAME?        #NAME?                          24    #NAME? #NAME?      #NAME?
   25       #NAME?      #NAME?       #NAME?        #NAME?                          25    #NAME? #NAME?      #NAME?
   26       #NAME?      #NAME?       #NAME?        #NAME?                          26    #NAME? #NAME?      #NAME?
   27       #NAME?      #NAME?       #NAME?        #NAME?                          27    #NAME? #NAME?      #NAME?
   28       #NAME?      #NAME?       #NAME?        #NAME?                          28    #NAME? #NAME?      #NAME?
   29       #NAME?      #NAME?       #NAME?        #NAME?                          29    #NAME? #NAME?      #NAME?
   30       #NAME?      #NAME?       #NAME?        #NAME?                          30    #NAME? #NAME?      #NAME?
   31       #NAME?      #NAME?       #NAME?        #NAME?                          31    #NAME? #NAME?      #NAME?
   32       #NAME?      #NAME?       #NAME?        #NAME?                          32    #NAME? #NAME?      #NAME?
   33       #NAME?      #NAME?       #NAME?        #NAME?                          33    #NAME? #NAME?      #NAME?
   34       #NAME?      #NAME?       #NAME?        #NAME?                          34    #NAME? #NAME?      #NAME?
   35       #NAME?      #NAME?       #NAME?        #NAME?                          35    #NAME? #NAME?      #NAME?
   36       #NAME?      #NAME?       #NAME?        #NAME?                          36    #NAME? #NAME?      #NAME?
   37       #NAME?      #NAME?       #NAME?        #NAME?                          37    #NAME? #NAME?      #NAME?
   38       #NAME?      #NAME?       #NAME?        #NAME?                          38    #NAME? #NAME?      #NAME?
   39       #NAME?      #NAME?       #NAME?        #NAME?                          39    #NAME? #NAME?      #NAME?
   40       #NAME?      #NAME?       #NAME?        #NAME?                          40    #NAME? #NAME?      #NAME?
   41       #NAME?      #NAME?       #NAME?        #NAME?                          41    #NAME? #NAME?      #NAME?
   42       #NAME?      #NAME?       #NAME?        #NAME?                          42    #NAME? #NAME?      #NAME?
   43       #NAME?      #NAME?       #NAME?        #NAME?                          43    #NAME? #NAME?      #NAME?
   44       #NAME?      #NAME?       #NAME?        #NAME?                          44    #NAME? #NAME?      #NAME?
   45       #NAME?      #NAME?       #NAME?        #NAME?                          45    #NAME? #NAME?      #NAME?
   46       #NAME?      #NAME?       #NAME?        #NAME?                          46    #NAME? #NAME?      #NAME?
   47       #NAME?      #NAME?       #NAME?        #NAME?                          47    #NAME? #NAME?      #NAME?
   48       #NAME?      #NAME?       #NAME?        #NAME?                          48    #NAME? #NAME?      #NAME?
   49       #NAME?      #NAME?       #NAME?        #NAME?                          49    #NAME? #NAME?      #NAME?
   50       #NAME?      #NAME?       #NAME?        #NAME?                          50    #NAME? #NAME?      #NAME?
   51       #NAME?      #NAME?       #NAME?        #NAME?                          51    #NAME? #NAME?      #NAME?
   52       #NAME?      #NAME?       #NAME?        #NAME?                          52    #NAME? #NAME?      #NAME?
   53       #NAME?      #NAME?       #NAME?        #NAME?                          53    #NAME? #NAME?      #NAME?
   54       #NAME?      #NAME?       #NAME?        #NAME?                          54    #NAME? #NAME?      #NAME?
   55       #NAME?      #NAME?       #NAME?        #NAME?                          55    #NAME? #NAME?      #NAME?
   56       #NAME?      #NAME?       #NAME?        #NAME?                          56    #NAME? #NAME?      #NAME?
   57       #NAME?      #NAME?       #NAME?        #NAME?                          57    #NAME? #NAME?      #NAME?
   58       #NAME?      #NAME?       #NAME?        #NAME?                          58    #NAME? #NAME?      #NAME?
   59       #NAME?      #NAME?       #NAME?        #NAME?                          59    #NAME? #NAME?      #NAME?
   60       #NAME?      #NAME?       #NAME?        #NAME?                          60    #NAME? #NAME?      #NAME?
   61       #NAME?      #NAME?       #NAME?        #NAME?                          61    #NAME? #NAME?      #NAME?
   62       #NAME?      #NAME?       #NAME?        #NAME?                          62    #NAME? #NAME?      #NAME?
   63       #NAME?      #NAME?       #NAME?        #NAME?                          63    #NAME? #NAME?      #NAME?
   64       #NAME?      #NAME?       #NAME?        #NAME?                          64    #NAME? #NAME?      #NAME?
   65       #NAME?      #NAME?       #NAME?        #NAME?                          65    #NAME? #NAME?      #NAME?
   66       #NAME?      #NAME?       #NAME?        #NAME?                          66    #NAME? #NAME?      #NAME?
   67       #NAME?      #NAME?       #NAME?        #NAME?                          67    #NAME? #NAME?      #NAME?
   68       #NAME?      #NAME?       #NAME?        #NAME?                          68    #NAME? #NAME?      #NAME?
   69       #NAME?      #NAME?       #NAME?        #NAME?                          69    #NAME? #NAME?      #NAME?
   70       #NAME?      #NAME?       #NAME?        #NAME?                          70    #NAME? #NAME?      #NAME?
   71       #NAME?      #NAME?       #NAME?        #NAME?                          71    #NAME? #NAME?      #NAME?
   72       #NAME?      #NAME?       #NAME?        #NAME?                          72    #NAME? #NAME?      #NAME?
   73       #NAME?      #NAME?       #NAME?        #NAME?                          73    #NAME? #NAME?      #NAME?
   74       #NAME?      #NAME?       #NAME?        #NAME?                          74    #NAME? #NAME?      #NAME?
   75       #NAME?      #NAME?       #NAME?        #NAME?                          75    #NAME? #NAME?      #NAME?
   76       #NAME?      #NAME?       #NAME?        #NAME?                          76    #NAME? #NAME?      #NAME?
   77       #NAME?      #NAME?       #NAME?        #NAME?                          77    #NAME? #NAME?      #NAME?
   78       #NAME?      #NAME?       #NAME?        #NAME?                          78    #NAME? #NAME?      #NAME?
   79       #NAME?      #NAME?       #NAME?        #NAME?                          79    #NAME? #NAME?      #NAME?
   80       #NAME?      #NAME?       #NAME?        #NAME?                          80    #NAME? #NAME?      #NAME?
   81       #NAME?      #NAME?       #NAME?        #NAME?                          81    #NAME? #NAME?      #NAME?
   82       #NAME?      #NAME?       #NAME?        #NAME?                          82    #NAME? #NAME?      #NAME?
   83       #NAME?      #NAME?       #NAME?        #NAME?                          83    #NAME? #NAME?      #NAME?
   84       #NAME?      #NAME?       #NAME?        #NAME?                          84    #NAME? #NAME?      #NAME?
   85       #NAME?      #NAME?       #NAME?        #NAME?                          85    #NAME? #NAME?      #NAME?
   86       #NAME?      #NAME?       #NAME?        #NAME?                          86    #NAME? #NAME?      #NAME?
   87       #NAME?      #NAME?       #NAME?        #NAME?                          87    #NAME? #NAME?      #NAME?
   88       #NAME?      #NAME?       #NAME?        #NAME?                          88    #NAME? #NAME?      #NAME?
   89       #NAME?      #NAME?       #NAME?        #NAME?                          89    #NAME? #NAME?      #NAME?
   90       #NAME?      #NAME?       #NAME?        #NAME?                          90    #NAME? #NAME?      #NAME?
   91       #NAME?      #NAME?       #NAME?        #NAME?                          91    #NAME? #NAME?      #NAME?
   92       #NAME?      #NAME?       #NAME?        #NAME?                          92    #NAME? #NAME?      #NAME?
   93       #NAME?      #NAME?       #NAME?        #NAME?                          93    #NAME? #NAME?      #NAME?
   94       #NAME?      #NAME?       #NAME?        #NAME?                          94    #NAME? #NAME?      #NAME?
   95       #NAME?      #NAME?       #NAME?        #NAME?                          95    #NAME? #NAME?      #NAME?
   96       #NAME?      #NAME?       #NAME?        #NAME?                          96    #NAME? #NAME?      #NAME?
   97       #NAME?      #NAME?       #NAME?        #NAME?                          97    #NAME? #NAME?      #NAME?
   98       #NAME?      #NAME?       #NAME?        #NAME?                          98    #NAME? #NAME?      #NAME?
   99       #NAME?      #NAME?       #NAME?        #NAME?                          99    #NAME? #NAME?      #NAME?
  100       #NAME?      #NAME?       #NAME?        #NAME?                         100    #NAME? #NAME?      #NAME?

								
To top