187x Filetype XLSX File size 0.15 MB Source: fingertips.phe.org.uk
Sheet 1: Intro
Analytical Tools | ||||||||||
for Public Health | February 2018 | |||||||||
Commonly used public health statistics and | ||||||||||
their confidence intervals | ||||||||||
Contents | This spreadsheet provides Excel formulæ for calculating the more | |||||||||
common types of statistic used within public health intelligence. | ||||||||||
Proportions | These include rates, proportions, means and age-standardised | |||||||||
rates and ratios. Formulæ are also given for calculating | ||||||||||
Rates | confidence intervals for all of these statistics. | |||||||||
Means | This tool was designed and produced specifically to accompany | |||||||||
the APHO Technical Briefing on Commonly used public health | ||||||||||
Indirectly Standardised Ratios (e.g. SMRs) | statistics and their confidence intervals, available at | |||||||||
https://fingertips.phe.org.uk/profile/guidance. | All the formulæ in the | |||||||||
Technical Briefing are replicated here, with examples of their | ||||||||||
Directly Standardised Rates | calculation in Excel. It will act as a tutorial for those unfamiliar | |||||||||
with the statistical formulæ. | ||||||||||
The examples given here may be copied into other spreadsheets | ||||||||||
for multiple calculations. | ||||||||||
Version History | ||||||||||
February 2018 | Tool re-released with PHE branding. | |||||||||
CI methods for DSRs, ISRs and rates brought in | ||||||||||
line with current guidance: rates and ISRs use | ||||||||||
exact CI method for numerators <10 and Byar's CI | ||||||||||
method for numerators >=10. DSRs use Byar's CI | ||||||||||
method for numerators >= 10 and are not | ||||||||||
calculated for numerators <10. | ||||||||||
February 2014 | Tool updated to accommodate extended age bands | |||||||||
and to use 2013 European Standard Population | ||||||||||
March 2008 | First release of APHO tool of same name | |||||||||
Proportions | ||||||
Numerator | Denominator | Proportion | 95% Confidence Interval | |||
O | n | p | plower | pupper | ||
65 | 100 | 0.65 | 0.553 | 0.736 | ||
Confidence level | Percentage | 95% Confidence Interval | ||||
100(1–a)% | p% | plower% | pupper% | |||
95.0% | 65.0 | 55.3 | 73.6 | a = | 0.05 | |
Data Entry | ||||||
Cells with a white background are intended to be overwritten by the user. | ||||||
Cell B6 is the numerator observed number of individuals in the sample/population | ||||||
having the specified characteristics (O). | ||||||
Cell C6 is the denominator total number of individuals in the sample/population (n). | ||||||
Note that O must be at least zero and not greater than n: if it is not then an error will | ||||||
be returned. If O can legitimately be greater than n then this is not a proportion: it | ||||||
may be appropriate to use the Rates worksheet. | ||||||
Cell B10 is the confidence level required for the confidence interval, most commonly | ||||||
95% but 99% or 99.8% confidence intervals are also frequently used. | ||||||
The calculations return the results as a proportion and as a percentage: the latter is | ||||||
simply the proportion multiplied by 100. If copying the formula and pasting into other | ||||||
spreadsheets, either format works independently of the other, so the cells not required | ||||||
can be deleted and the cells moved into an appropriate arrangement. | ||||||
Methodology | ||||||
The methodology is explained in detail in the APHO Technical Briefing on Commonly | ||||||
Used Public Health Statistics and their Confidence Intervals. The formula numbers | ||||||
below correspond to those in the briefing. | ||||||
The values given on the right are given to explain the Excel formulæ step-by-step. | ||||||
However, the calculations above stand alone and may be copied without any of the | ||||||
calculations below this point. | ||||||
The proportion p is given by: | Formula 1 | p = | 0.65 | |||
where: | ||||||
O is the numerator observed number of individuals in the sample/population | O = | 65 | ||||
having the specified characteristics; | ||||||
n is the denominator total number of individuals in the sample/population. | n = | 100 | ||||
Using the Wilson Score method1,2, the 100(1–a)% confidence limits for the proportion | ||||||
p are given by: | ||||||
Formula 2a | plower = | 0.5525 | ||||
Formula 2b | pupper = | 0.7364 | ||||
where: | ||||||
q is 1–p; | q = | 0.35 | ||||
z is the 100(1–a/2)th percentile value from the Standard Normal distribution. | z = | 1.9600 | ||||
For example, for a 95% confidence interval, a = 0.05 and z = 1.96 (i.e. the | ||||||
97.5th percentile value from the Standard Normal distribution). | ||||||
Reference | ||||||
1: Wilson EB. Probable inference, the law of succession, and statistical inference. J Am Stat Assoc | ||||||
1927; 22: 209–12. | ||||||
2: Newcombe RG, Altman DG. Proportions and their differences. In Altman DG et al. (eds). Statistics | ||||||
with confidence (2nd edn). London: BMJ Books; 2000: 46–8. |
Rates | ||||||
Numerator | Denominator | Rate | 95% Confidence Interval | |||
O | n | (r×100,000) | lower | upper | ||
65 | 100 | 65000.0 | 50163.2 | 82849.1 | ||
Confidence level | Multiplier | |||||
100(1–a)% | ||||||
95.0% | 100,000 | a = | 0.05 | |||
Data Entry | ||||||
Cells with a white background are intended to be overwritten by the user. | ||||||
Cell B6 is the numerator number of observed events (O). | ||||||
Cell C6 is the denominator population-years at risk (n). | ||||||
Note that O must be at least zero: if it is not then an error will be returned. | ||||||
Cell E10 is the confidence level required for the confidence interval, most commonly | ||||||
95% but 99% or 99.8% confidence intervals are also frequently used. | ||||||
Cell F10 allows the user to enter a multiplier for presentation of the rate and its | ||||||
confidence interval. | ||||||
Methodology | ||||||
The methodology is explained in detail in the APHO Technical Briefing on Commonly | ||||||
Used Public Health Statistics and their Confidence Intervals. The formula numbers | ||||||
below correspond to those in the briefing. | ||||||
The values given on the right are given to explain the Excel formulæ step-by-step. | ||||||
However, the calculations above stand alone and may be copied without any of the | ||||||
calculations below this point. | ||||||
The rate of events r is given by: | Formula 3 | r = | 0.65 | |||
where: | ||||||
O is the numerator number of observed events; | O = | 65 | ||||
n is the denominator population-years at risk. | n = | 100 | ||||
The 100(1–a)% confidence limits for the rate r are given by: | ||||||
Formula 4a (i) | rlower = | 0.5016 | ||||
Formula 4b (i) | rupper = | 0.8285 | ||||
where: | ||||||
Olower and Oupper are the lower and upper confidence limits for the observed | ||||||
number of events. | ||||||
Using Byar's method1, the 100(1–a)% confidence limits for the observed number | ||||||
of events are given by: | ||||||
Formula 4a (ii) | Olower = | 50.1632 | ||||
Formula 4b (ii) | Oupper = | 82.8491 | ||||
where: | ||||||
z is the 100(1–a/2)th percentile value from the Standard Normal distribution. | z = | 1.9600 | ||||
For example, for a 95% confidence interval, a = 0.05 and z = 1.96 (i.e. the | ||||||
97.5th percentile value from the Standard Normal distribution). | ||||||
For small numerators, Byar's method can be less accurate and an exact method | ||||||
based on the Poisson distribution can be used. For 95% confidence intervals, | ||||||
Byar's method is within 0.2% of the exact value for numerators of 10 or more. For | ||||||
99.8% confidence intervals it is within 1.5% of the exact value for numerators of at | ||||||
least 10, but it always errs on the conservative side, ie confidence limits are slightly | ||||||
wider than the exact ones. | ||||||
Using the link between the Poisson and c2 distributions2, the equations for Olower and | ||||||
Oupper above can be replaced by: | ||||||
Olower = | 50.1656 | |||||
Oupper = | 82.8478 | |||||
where: | ||||||
c2lower is the 100(1–a/2)th percentile value from the c2 distribution with 2O | c2lower = | 100.3313 | ||||
degrees of freedom; | ||||||
c2upper is the 100(a/2)th percentile value from the c2 distribution with 2O+2 | c2upper = | 165.6957 | ||||
degrees of freedom. | ||||||
This spreadsheet uses Excel's built-in functions for exact probabilities for all cases | ||||||
based on numerators under 10, in order to give the most accurate results. | ||||||
References | ||||||
1: Breslow NE, Day NE. Statistical methods in cancer research, volume II: The design and analysis of | ||||||
cohort studies. Lyon: International Agency for Research on Cancer, World Health Organisation; 1987. | ||||||
2: Armitage P, Berry G. Statistical methods in medical research (3rd edn). Oxford: Blackwell; 1994. |
no reviews yet
Please Login to review.