Graphing multiple years of data on one chart with inconsistent date data points

**steve-p** · 07-30-2012, 03:57 PM

I have a spreadsheet where I'm recording years worth of solar generation data. I record the date, the KWh on the Meter, and the KWh generated on the inverter, and then calculate how much grid energy I use per day, how much solar I generate per day, and how much electricity I use overall per day between the latest recording and the previous recording. The "periodicity" of the dates vary greatly (occasionally a month will go by), but the calculations take into account the number of days between measurements to come up with "average" grid, generated, and usage numbers.

I've been keeping this data since 2009, and want to create a yearly comparison chart. But I can't figure out how to properly add each years series to properly plot ALL the datapoints. It seems that an entry will only get plotted if I managed to record data for the same date in two or more separate years.

My "raw data" is laid out in the following fashion:

Please Login or Register  to view this content.

I have the graph almost done, but it will only show entries where I managed to take measurements on the same day in two or more years. For example, in the attached image, I have 2012 data going clear up to current (end of July), but the last 2012 entry that matched another year was April 11th.

Is there a way to get Excel to map all the points, regardless of whether or not another date matches?

I'll attach the following files in Solar-Excel.zip:
solar.jpg : A picture of the graph
2009.png: A snapshot of the data series definition for 2009
2010.png: A snapshot of the data series definition for 2010
2011.png: A snapshot of the data series definition for 2011
2012.png: A snapshot of the data series definition for 2012
solar.csv: The data in CSV format
Solar.xlsx: The workbook I'm working from

I'd appreciate any hints, tips, etc on how I can get this to "graph accurately" (show all values)...

Steve