Outliers In Graphs

[Mordred]

Hi all,

In the Graphs sheet in the provided workbook, Chart 4 - % Changes has an outlier in Aug-09. Because of this, the graph lines look too combined and are not easy to view. Is there a way to take care of this problem. I tried setting the scale for the year 2009 but it affects the whole chart and not that specific area. Any suggestions?

Thanks.

[squiggler47]

You could strip it out and perhaps make a new data series which is on a new axis, label the point with a value so the scale doesnt make it look odd!

On my box plot I used the standard 1.33* 2nd quartile to strip out outliers!

[Mordred]

The chart is not meant for statistical purposes though. It is just a running-scroll chart that plots each value for each month's percentage change starting from 2006 to present. At one point, there is quite the dip for one value and because of this, I have had to set my scale the Y axis to match this dip. I like the sounds of stripping it out and creating a new data series with it but i would want to be in the same chart area. I am unclear as to what exactly I want from this as charting is a new concept to me.

[squiggler47]

Its always a problem when you get spikes as to what to do with them!

this may possibly give you an idea :-

http://chandoo.org/wp/2010/08/20/cha...-large-values/

[squiggler47]

you can have 2 axis on one chart, just by selecting it in the dataseries options!

It would still plot on the same chart, but would let you shrink the scale of the outliers, to make it more readable!

[Mordred]

Thanks squiggler, I'm going to try the broken Y-axis technique and test my skills. I don't know how I'll do this given the chart is scrolling but there has to be a way.

[Mordred]

I don't fully know how to describe what I've done here but I double clicked the Value Axis, clicked Scale, and ticked the min and max scales that I wanted for the desired areas where I needed a different min and max, and now all is good.

[Mordred]

I don't know about what I did. It seems that by changing one scale at a point in the graph, that Excel auto changes the rest of the points in the graph. So I now have ranges that are all over the place to the point where the scrolling of the chart looks horrible. This is not solved yet like I had thought.

[Andy Pope]

The checkmark will make the scale Auto. So the scale will automatically adjust the values for the data being plotted.

Moving the series with the spike data point to the secondary axis will help with the primary scale. But it will make it impossible to compare that series with the others.

If you do implement the broken Y axis technique you will have problems with the additional data series appearing in the data table.

You might want to consider the appearance of your charts. I would suggest making the plot area white rather than grey. Also tone down the gridlines by using a light grey dashed lines.

[squiggler47]

Just as a thought, does the chart impart any more information without that point?

Why I suggested the second axis, is that the point would be highlighted and since the table is there already, the table can be referred to for the value as long as the point outlies the data enough it would act like a log scale perhaps using a macro to make the smaller scale fit inside the larger one by the right ratio!

I definitely agree with Andy, I hate the grey background default!

[Mordred]

Alright the grey is gone. I put it there because I wanted the graphs to look isolated in groups of two, since they used the same data in groups of two.

[Andy Pope]

We were talking about the grey in the plot area. The part where the lines and gridlines are.
The grey shading of the cells to group pairs of charts is okay.