Funnel plots for count data in Tableau – brute force approach

Many years ago, my former employer (and precursor to Public Health England or PHE) – the Association of Public Health Observatories, released a series of Excel spreadsheets in which the basic statistical calculations for various funnel plots were described and demonstrated.  You can still find these resources online (proportions, rates and DSRsISRs and SMRs, counts).  If you don’t know what the difference is between a rate, proportion or a count (nor how to identify them), read the accompanying guide here.

Put simply, a funnel plot is a two-axis control chart that takes into account sample size when performing significance testing.  It places a measure of variation on the Y-axis and a measure of population (or sample size) on the X-axis.  The control limits become tighter as the associated population becomes larger – hence the funnel.  This principle can be applied to a variety of measures, albeit with different statistical underpinnings. Read more

Hexmaps – the old fashioned (stupid) way

Some time ago, a colleague had been impressed by the Guardian’s hexmaps of the 2015 general election. He issued a challenge to try and recreate the basic premise using whatever software and techniques we had available and some uncontraversial data (population and deprivation data).

The basic idea of this is that:

  • constituencies are given a block of hexagons, the amount of which is proportionate to their population;
  • those hexablocks are supposed to look like the constituencies themselves
  • the hexablocks are then stuck together and ‘tessalate’ with eachother
  • the shared borders of the constiuency should be reflected by it’s hexablock
  • the overall aggregation of these hexablocks should look recognisably like the shape of it’s parent (in the Guardian’s case, the UK; in my case, Wakefield district)

Read more

Convert Easting and Northing Coordinates to Latitude and Longitude using VBA

Many Government bodies and agencies  in the UK still publish postcode data using Easting and Northing co-ordinates.  Although this may be useful for some, many software systems, such as Tableau, don’t natively support this and prefer the more universally used Latitude and Longitude coordinate system.

Converting to and from these coordinate systems is not a perfect science.  The process is an approximation as it attempts to convert 2-dimensional mappings onto a 3-dimensional object, along with taking account of some of the obscurities of the OSGB36 grid system.  You can read more about the UK’s grid system at the Ordinance Survey. Read more

Automatically backing up your Excel files

Perhaps you have a spreadsheet that is absolutely critical?  Something that, if lost or corrupted, would end your world.  Maybe it contains rankings for your favourite Star Trek episodes (TNG:E5x25 “The Inner Light” FWIW)  or something more trivial like the company’s finances?

Either way, it would be a good idea to back it up to protect against both loss and ballsing up some convoluted formulas you wrote, but the chances of you being able to remember to do this regularly are pretty much nil.

The sensible person might ask why store such business-critical data in a spreadsheet, rather than in a server environment with automatic backup.  Such people haven’t always had the pleasure of working in organisations where the TV is referred to as “thar’ magic box o’light pixies”.

Instead of such trickery, you can instruct the file to back itself up.  In this instance, I prefer to have the file make a copy of itself every time I save.  You may prefer to use a different workbook event. Read more