Life Expectancy

This relates to a dashboard I have created from the Male Life Expectancy data file that I downloaded the other day (link below). It looks like this:

You can download the entire file from the link at the end of this page.

Rather than describe in detail how I put this together I will simply tell you what this dashboard does and then tell you the Excel features I used to create it.

The file on which this dashboard is based contains the Male Life Expectancy values for 248 countries/regions covering the period 1960 – 2013. The raw data are in the form of years, such as 64.46 … years. Nice and straightforward. The data were provided by the UN, Eurostat, and the US Census Bureau.

There are two main elements to the dashboard: the calculation of the rate of change and a graphical presentation of the data.

The screenshot above shows the results of the UK and Bhutan. Notice that each of the two choices you can make of which countries’ data to show are taken by using combo boxes which have been placed on cells A4 and E4 on the Comparisons tab.

The rate of change is found using an exponential function, which is akin to the mathematics used to calculate a learning curve. What this means is that if, for example, the learning rate, LR, is shown as 96.89% (this is the rate for the UK) it means that life expectancy in that country has improved over the 53 year period but only slowly. Bhutan has an LR of 84.19% which means that it has increased its life expectancy at a much faster rate than the UK, at 96.89%.

The graphs show the expectancy data in terms of the CAT or cumulative average time: this is a standard metric for a learning curve calculation. The steeper the curve, the greater the learning that takes place: conversely, the lower the percentage LR, the faster that learning has taken place!

Look at the cell I26 on the Comparisons tab to see how to find the CAT

=IFERROR(VLOOKUP(A$4,Data!$BI$6:$DM$253,3+H26),”No Data”)

My idea was that you would choose a base country using the combo box on cell A4 and then use the other selection as a comparator country: thus showing two countries at a time.

The Sparklines are primitive and I am in two minds as to whether I should encourage you to delete them or not: I probably would as they don’t really add a lot of value!

In total these are the Excel features I have used in preparing this analysis:

• raw data which has been left in tact on the Data tab
• index numbers which I have calculated and on which the learning data are based: Data tab
• combo box
• Sparklines
• IFERROR
• SLOPE
• LN
• EXP
• Charts
• x axis label linked to summary results

Please feel free to play around with this file and it is meant to be used as a template: find the equivalent female life expectancy data and simply copy and paste it into this file and everything should be calculated and presented automatically.

Download the file from here: life_expect_learn_curve_applied

Source of data: http://data.worldbank.org/indicator/SP.DYN.LE00.MA.IN

Duncan Williamson