Hey folks, I've got a tough one here (or so I think):

I've created a TTM (trailing twelve month) report and have it graphed successfully in one consecutive line, however, when attempting to plot year over year using named ranges, the scale seems off.

I've attached the spreadsheet. Focusing on the 12 year charts, I have a worksheet called "Trailing 12" that shows the graph in a consecutive line. "Trailing 12 YoY" is my attempt to plot the same data parsed by year. "12 Data" contains the values I'm plotting (including named ranges) from columns C & D - A & B can be ignored.

The graph only plots workdays and for my category label, I just chose a sample year with the most data points - 2008. 2007 and 2009 don't match up with every 2008 workday, and don't have as many data points. I adjusted the scale to show from 1/1 through 12/31 (instead of using minimum and maximum). 2007 and 2008 seem to show the same date range, but the raw data shows different ranges for these years, so I'm not sure why they appear to have the same scale.

Maybe it's not possible to plot cleanly... I don't know. I'm not a novice, but am not an expert either. All help is greatly appreciated!

-Mike

Howdy. It is has been a while since I have done any of this, but I think you need to set up some helper columns/rows to determine the day of the month for each year and then match those. But you also need month and year column/row helpers. It takes a while to get there, but it does work.

That's the route I went with, and seems to be working well. I extracted the day/month and parsed into separate columns by year. Plotting from there was relatively easy.

Another route I had tried was to plot a combination chart. I plotted one year as a linear chart, and the other years as an XY (scatter chart) in the same graph. I think the first approach was much easier. Thanks for the help!

Glad to hear that it worked.