Help with Excel chart - how to plot intervals of different length in a graph

[Tanja]

Hi there,

My brain is just not working properly at the moment. I was asked to create some charts (my boss wanted line graphs) showing how our organisation was performing over time in certain measures. Easy enough. However, the problem I have is that for the first part of the year I have only quarterly data, and for the third quarter I have monthly data (so I have Q1, Q2, October, November, December). I know it wouldn't be acceptable to put these intervals on one line graph as the intervals are of different length (this is what my boss wants me to do but I don't think it's acceptable).

Now, most of the interesting stuff (improvements in our work) happened since October. I can see I have two choices - add the data for the last three months into a quarterly value, and just plot the three quarters, or ditch the first two quarters and just present the more interesting monthly data for last three months. Is there a third option that can keep all the data in one graph? How would I represent this? Any thoughts are appreciated.

 

[Dats]

Could you put in the Q1 and Q2 data three times to make it three times as long on the graph?

 

[pgwenthold]

Just plot the quarterly data against selected month from the quarter. Make it Feb, May, August, and Nov, for example. Then plot the line on the same graph as the monthly data.

That should work, if I am actually clear enough in what I am saying so that you can understand.

 

[Wudang]

Or do you need to assign 1/3 of the Q1 data to jan, Feb and March etc?

 

[Lothian]

I would go for a monthly chart but for q1-3 divide the /quarterly total by 3.The alternative is to do two columns and therefore two lines on the chart. One for the quartely figures and one for the monthly you can also put different axis on each side.of the chart.

 

[Worm]

It very much depends on the data, and if/how quarterly figures can be compared to monthly figures.

In general, I tend to approach situations like this by having monthly and quarterly data alongside each other in a bar chart, in different colours. so the X-axis looks like :

Apr May Jun Q1 Jul Aug Sep Q2 Oct Nov Dec Q3 Jan Feb Mar Q4

This would be quite good if you can get Q3 data as well as Oct/Nov/Dec

If that makes sense.

 

[gumboot]

The obvious solution, to my thinking, would be to get more data. It must exist somewhere, right?

 

[Tanja]

Thanks all for your suggestions. At the end I went with showing the data as monthly and averaging the first two quarters and explaining in the notes what I did. Thank you for all your suggestions.