Excel Spreadsheet Calculations 46192 | Phe Tool For Common Ph Stats And Cis

Partial file snippet.

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
		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	
	
		

		

		

		

		

		

		

		

		

		

		

	



Sheet 2: Proportions

	
	
	
	
		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.
		

		

		

		

		

		

	



Sheet 3: Rates

	
	
	
	
		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.

The words contained in this file might help you see if this file matches what you are looking for:

...Sheet intro analytical tools for public health february commonly used statistics and their confidence intervals contents this spreadsheet provides excel formul aelig calculating the more common types of statistic within intelligence proportions these include rates means agestandardised ratios are also given all tool was designed produced specifically to accompany apho technical briefing on indirectly standardised eg smrs available at httpsfingertipspheorgukprofileguidance in replicated here with examples directly calculation it will act as a tutorial those unfamiliar statistical may be copied into other spreadsheets multiple calculations version history rereleased phe branding ci methods dsrs isrs brought line current guidance use exact method numerators lt byar s gt not calculated updated accommodate extended age bands european standard population march first release same name numerator denominator proportion interval o n p plower pupper level percentage ndash data entry cells white b...

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.

Related files

Share

Help


	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
Indirectly Standardised Ratios (e.g. SMRs)	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
Directly Standardised Rates	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

Related files

Share

Share to social media

Help

Login Area