Custom Charts in Excel :: Comparison RAG Chart Showing Tolerance Limits

Posted: June 13, 2013 by MaxFraudy in Excel
Tags: , ,

Tolerance - RAG ChartEarlier we had seen the Thermometer Chart that lets one see where a value is lying with reference to a scale. That works when you are measuring everything on the same scale and when the maximum is defined (hence a 100% stacked chart). At times, e.g. when comparing Actuals vs Targets, you might just want to see a RAG (Red-Amber-Green) picture. Let’s see this using an example. Let’s say that I have set targets for the four regions and I have Actuals data as below:

Tolerance - Raw Data

Raw Data

I have set a tolerance of 10% which means that as long as the Actuals are within 10% of the Targets, it’s acceptable otherwise it’s a matter of worry. Now to plot this, I have created two calculated columns as below:

Tolerance - Limits

Calculated Columns

Now, plot a stacked bar chart with this data. The last column is to plotted twice and the Target column avoided. It will look like this:

Tolerance - Raw Chart

Raw Chart

Now, do the following:

  • Move the ‘Actual’ series to secondary axis.
  • Make sure that the primary and the secondary axes have the same Minimum and Maximum.
  • Format the Actual series to increase Gap width.
  • Remove the legend.
  • Format all the series to get the desired colors. Now your chart should start looking like this:
Tolerance - RAG Chart

Final RAG Chart

Or you can go for a more subtle version of the same:

Tolerance - Subtle Chart

Subtle Chart

As usual, you can also download the file Tolerance

Previously in Custom Charts:

 

Advertisements
Comments
  1. Rajan says:

    Its great u remember we created a dial chart to do the same things

    • MaxFraudy says:

      yes… but in a dial chart, you can see only one value at a time. whereas here, one chart can give the whole picture. by the way, why don’t you share the dial chart.

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s