License

Licensed under a Creative Commons License

Disclaimer

All data and information provided on this site is on an as-is basis and for informational purposes only. We do neither guarantee for accuracy, completeness, suitability or validity of information on this blog nor be liable for any errors in this information or any damages arising from its use. This blog may contain links to other web sites. We do not have any control over the content contained on those sites.

Tuesday, February 17, 2009

Visiting a friend

Chandoo's KPI Dashboards revisited - the Box Plots

In summer 2008 my friend and Microsoft Excel MVP Chandoo was kind enough to give me the opportunity of contributing guest posts to his excellent blog Chandoo.org. Actually Chandoo even featured a whole 6 post series on how to create interactive KPI dashboards with Microsoft Excel. Here is a screenshot of the final dashboard:

The last part of the series was about box plots to visualize the distribution of the data (Box Plots Excel Dashboards Tutorial), including average and target values. At that time Chandoo and I decided to apply Occam’s razor and we restricted the tutorial to a simplified version of box plots, working only for data sets with positive values.

This follow-up post on my own blog is about how to create these box plots for all kind of data distribution, i.e. positive and negative values.

The technique of making these box plots applicable to data regardless the sign is pretty much the same as already described in the original post on Chandoo.org:

Use a stacked bar chart with one category and several data series You need 8 instead of 4 dummy data series for the universal solution

Write formulas to calculate the values of the 8 data series It took me some time to figure out the correct combination, but the formulas themselves are not too complicated: nested IFs, MIN and MAX (for details see the workbook, download link below)

Format the bars according to their position on the stack: invisible, light grey, dark grey and so on

Add the average and the target values as additional series to the chart and change the chart type of these new series to XY scatter charts (X is the average / target value, Y is a dummy 1)

Set the scale of the secondary vertical axis to minimum 0 and maximum 2 in order to position the average and target aligned with the bars

Format the average and the target the way you want In our example: a cross for the average and a vertical line (using vertical error bars) for the target

Get rid of the chart junk No fill color and no border for plot or chart area; no line, tick marks etc. for the vertical axes, etc.

Comments

Visiting a friend

Chandoo's KPI Dashboards revisited - the Box Plots

In summer 2008 my friend and Microsoft Excel MVP Chandoo was kind enough to give me the opportunity of contributing guest posts to his excellent blog Chandoo.org. Actually Chandoo even featured a whole 6 post series on how to create interactive KPI dashboards with Microsoft Excel. Here is a screenshot of the final dashboard:

The last part of the series was about box plots to visualize the distribution of the data (Box Plots Excel Dashboards Tutorial), including average and target values. At that time Chandoo and I decided to apply Occam’s razor and we restricted the tutorial to a simplified version of box plots, working only for data sets with positive values.

This follow-up post on my own blog is about how to create these box plots for all kind of data distribution, i.e. positive and negative values.