## Population Pyramid Graph v In Cell Graph

Introduction

On the brilliant chandoo.org and Jon Peltier sites/blogs, there is a discussion on the graphical presentation of murder and suicide rates from the USA. The discussion started when Jon Peltier found the Freakonomics web page,, and suggested that they had presented a rather ineffective graph of the information they were discussing. Jon Peltier then had a go at improving the graph and he presented a very effective dot graph.

chandoo.org picked up where Jon Peltier left off and he moved the discussion into the direction of in cell graphs: take a look at my recent page. chandoo took this idea significantly further than my page so please go to his page for those enhancements.

Population Pyramid Alternative

The point of this page, then, is to suggest that whilst there is a lot to commend Jon Peltier and chandoo, I think the following charts are even more effective, albeit they are based on just 16 States’ data and not the full 50 or so States available. You can download my Excel file from here: pop_pyramid_v_in_cell_dw

Firstly, I got the inspiration for my graph several years ago when I was researching the value chain and drew the following:

BA and easyJet are two UK based airlines and this chart illustrates their respective costs per 10,000 passenger kilometres … I think this is a very effective graph. So …

This graph shows the murder and suicide rates per 100,000 of the populations of the names States. Note that the suicide rate for all 16 States is virtually constant. Of course, I could have moved the vertical axis, like my first graph, to the left; I could have sorted it …

The following chart is also very revealing but it shows the absolute values and I appreciate that both Jon and chandoo were keen to illustrate the relativities as much as anything.

Finally the cross rates: murders/suicides v suicides/murders:

I like in cell graphs: using the REPT() function as well as sparklines but wish to offer the population pyramid as a good alternative for this debate.

Finally, here is a totally different graph altogether: if you plot the raw murders on the X axis and raw suicides on the Y axis, this fascinating relationship reveals itself:

Adding a linear trend line gives us the regression equation and r2 values of:

This shows a high level of correlation between the two variables and that there will be some suicides irrespective of whether there are any murders. Moreover, for every ten murders, there will be about 1.5 suicides, at national averages.

A grisly but fascinating topic!

Duncan Williamson

This site uses Akismet to reduce spam. Learn how your comment data is processed.