Tools

Providing support to other public health professionals is an important part of our role as the regional public health observatory. In addition to other assistance, we aim to disseminate good practice in methods for analysing health data.

Below is a list of health data analysis tools developed by our analysts as well as links to other organisations and their tools. We hope you will find them useful.

Excel Function For Directly Standardised Rates and Confidence Intervals

The Excel 2007/2010 Add-In, UDF_DSR_0710.xlam, and Excel 97-2003 Add-In, UDF_DSR_9703.xla (please make sure that the extension is .xla when you save the file), add custom functions to Excel to calculate directly standardised rates (DSRs) by age or age and sex and their confidence intervals. See the instructions for Excel 2007/2010 or Excel 97-2003 for details on installing and using the add-ins. These use the Public Health Observatories preferred methods for calculating confidence intervals for DSRs. For further details on this calculation please refer to Technical Briefing 3: Commonly used public health statistics and their confidence intervals and the accompanying tool.

WMPHO Smoking Model (2010)

The relationship between the percentage of smokers quitting, of never-smokers starting and of ex-smokers lapsing (starting again) and the future prevalence of smoking is not simple and depends among other things on the age at which these changes take place. WMPHO has built a model which allows you to explore how rates of quitting, starting and lapsing will affect future prevalence. It also allows one to explore how changing the percentage of smokers using different methods (Stop Smoking Services, OTC drug support only or cold turkey) and changing the efficacy of these methods would affect future prevalence.

The model is dynamic and based in a simple Excel workbook and contains data for all local authorities in the West Midlands. It can be downloaded from the link above.

Spine Chart Creator

WMPHO Spine Chart Creator

This is version 4 of an Excel tool WMPHO have produced to create spine charts as used in the APHO Health Profiles. These charts are used to present area-based data, either as profiles of an area or comparatively across areas, to clearly show statistical significance of variations. This version now allows users to choose the colours they wish to use for indicating significance, adjust the row height of the indicators in the chart and to insert headings into the chart. There is now also a manual version without macros, this may be easier to adapt locally.

Excel Function For Proportions and Percentages Confidence Intervals

Excel Function For Proportions and Percentages Confidence Intervals The WMPHO have written Excel Function Add-Ins to calculate Wilson Score Method confidence intervals¹ for proportions or percentages. This is the Association of Public Health Observatories preferred method for calculating confidence intervals for proportions, for further details on this calculation please refer to Technical Briefing 3: Commonly used public health statistics and their confidence intervals and the accompanying tool. You will need to download the instructions for Excel 2007 or Excel 97-2003 and follow the instructions contained to install and use the Excel 2007 Add-In, UDF_Wilson_07.xlam or the Excel 97-2003 Add-In, UDF_Wilson_9703.xla (please make sure that the extension is .xla when you save the file).

References:

Wilson EB. Probable inference, the law of succession and statistical inference. J Am Stat Assoc 1927;22:209-12

Excel Function For Confidence Intervals for Rates

Excel Function For Confidence Intervals for Rates The Excel 2007 Add-In, UDF_RateCI_07.xlam, and Excel 97-2003 Add-In, UDF_RateCI_9703.xla (please make sure that the extension is .xla when you save the file), are Excel Function Add-Ins to calculate confidence intervals for rates. This is the Association of Public Health Observatories preferred methods for calculating confidence intervals for rates. For further details on this calculation please refer to Technical Briefing 3: Commonly used public health statistics and their confidence intervals and the accompanying tool.

Also see the instructions for Excel 2007 or Excel 97-2003 for details on installing and using the add-ins.

Tools from other organisations:

Funnel plots

Analytical Tools for Public Health: Funnel plot for count data
This spreadsheet gives a template for producing funnel plots for count data - observed counts relative to expected counts. These could be counts of incidence or prevalence.

Analytical Tools for Public Health: Funnel plot for rates (including directly standardised rates)
This spreadsheet gives a template for producing funnel plots for rates, including directly standardised rates (DSRs).

Analytical Tools for Public Health: Funnel plot for proportions and percentages
This spreadsheet gives a template for producing funnel plots for proportions and percentages.

Analytical Tools for Public Health: Funnel plot for indirectly standardised ratios (SMRs)
This spreadsheet gives a template for producing funnel plots for indirectly standardised ratios (e.g. SMRs)
Commonly used public health statistics and their confidence intervals

Analytical Tools for Public Health: Commonly used public health statistics and their confidence intervals
A spreadsheet tool designed to accompany the Technical Briefing on Commonly used public health statistics and their confidence intervals, give examples of calculating the statistics and confidence intervals in Excel.

More tools from other sources can be found on the APHO website.