• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data point outside of range on modified vertical axis

Mike Rizza

Mike Rizza

New Member
I am doing compliance charts (% of items complying with policy). For this particular metric, management has decided that "green" is 94-100%, yellow is 90-94% and red is below 90%. (See attached sample and please excuse the color poor use of color... fixing that next!)

Compliance chart.jpg

I would like to lock the vertical axis from 85% to 100% for better visibility of performance, but what do I do when I get a data point off the chart? In the attached picture, January was 64%.

I don't want to change the axis when I get an outlier because I want to keep the focus on the normal control range and have the axis consistent across all metrics. I don't want to manually add data labels every time I have an outlier.

I am thinking of some ugly options where I make the chart do what I want through formula magic and special chart data, etc., but I am looking for some fresh ideas before I sink the time into something like that.

Are there other control charts I should consider here that would be better? I was looking at statistical quality control charts (like p chart, x-bar chart) but I think they will be too confusing for my audience that like simple charts and 3 colors.

Thanks for any input!
 
Exactly what I was looking for. Thanks for the links! One other link from the comments:
http://www.datadrivenconsulting.com/2009/12/charting-data-with-one-disproportionally-large-value/

I like a lot of these ideas, but they are better solutions when you only have a single chart. I'm going to have a page full of these, and can't do anything too special for the one with an outlier.

While I was at Jon Peltier's site, I found this and I think it might be my best answer so far:
http://peltiertech.com/Excel/ChartsHowTo/OutlierLabels.html
OutlierLabel08.gif
 
Hi Mike ,

This looks much better than the full-coloured one in your initial post ; the arrow and the label , are they dynamic i.e. in case there are points in between which have these values , will the arrow and the label appear at those points ?

Narayan
 
Right now I am setting the colors for the markers on each data point manually, as well as the data label. If a data point is below the y-axis, I am overriding the data point to be 85% so it is right on the y-axis and easier to read. Once I've got the formatting vetted, I will set up the necessary "helper cells" to make this all dynamic.

I've also learned a great new trick. Once I got the formatting right on one chart, I saved it as a template with Chart Tools > Design > Save As Template. Then I would go to my next chart, make sure I had all the right data ranges added (and in the same order) and then I would use Chart Tools > Design > Change Chart Type > Templates and select the template I just created and all my formatting would be applied to the next dashboard chart. Now all my charts are consistent with a few clicks rather than trying to duplicate my formatting precisely for each dashboard chart.
 
You must log in or register to reply here.
Back
Top