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

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)

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

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.

  • Spine Chart Tool (version 4) is an Excel tool WMPHO have produced to create spine charts as used in the APHO Health Profiles. 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. (Please note changing the layout of the spreadsheet may stop this template functioning correctly)
  • Manual Spine Chart Tool, this version is without macros, this may be easier to adapt for use locally. (You will need to set the template up for the number of indicators you are using)
  • Manual SPC Spine Chart Tool v1 a new version of the manual tool which uses Statistical Process Control (SPC) limits instead of an interquartile range.

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 intervals1 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:
  1. 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:

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