jagomart
digital resources
picture1_Excel Spreadsheet Calculations 46192 | Phe Tool For Common Ph Stats And Cis


 187x       Filetype XLSX       File size 0.15 MB       Source: fingertips.phe.org.uk


File: Excel Spreadsheet Calculations 46192 | Phe Tool For Common Ph Stats And Cis
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 aelig for calculating the more ...

icon picture XLSX Filetype Excel XLSX | Posted on 17 Aug 2022 | 3 years ago
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...

no reviews yet
Please Login to review.