Excel: Connecting the dots between inconsistent data

October 15, 2006 10:16 AM

Excel: I am trying to create a line graph of last year's workload versus this year's workload. Problem is, I don't have consistent data and my graphs have numerous gaps between data points. How can I best fill these gaps?

When making the graph, I can't figure out how to get Excel to just ignore missing data and connect the dots between the data I do have. This would be an adequate solution for me because I'm just looking at trends; not quantitative specifics.

I have tried using the "fill series" function to fill in the missing numbers but the numbers it provides are wildly inaccurate.

If anybody can tell me how to get Excel to connect the dots, or if a better solution is in order, I would appreciate it.

Thanks

posted by Ziggy Zaga to computers & internet (6 answers total)

Does this work for you?
posted by willnot at 10:59 AM on October 15, 2006 [1 favorite]

If you're looking at the trend I think you're going to want to make a scatterplot, but I don't exactly understand what you're graphing. Are the data paired?

Perhaps give us a subset of the data?
posted by nekton at 11:27 AM on October 15, 2006

(1) Don't make a line graph, just throw up the points in a scatterplot which won't care about missing data. Add the line in your head or with a pen and ruler.
(2) Linearly interpolate the missing data before you make the graph.
(3) Use something other than Excel (Stata, probably R), throw up the scatterplot vs. the time variable, and then add a lowess smoother line.
posted by ROU_Xenophobe at 11:45 AM on October 15, 2006

If you don't have the data, I recommend you don't even attempt to 'Fill in the blanks'.

It sounds like you do want a line graph, but format the lines to be transparent and the points extra large for visibility.
posted by Lanark at 12:01 PM on October 15, 2006

to compare two data series trends to each other - from Excel help what you might be looking for is as follows:

Add a trendline to a data series

Click the data series to which you want to add a trendline or moving average.

On the Chart menu, click Add Trendline.

On the Type tab, click the type of regression trendline or moving average you want.
posted by ptm at 1:58 PM on October 15, 2006

willnot: That looks almost exactly like what I need to accomplish. I'm putting off marking that as best answer until I can actually try it.

Thanks for all the responses!

All I'm graphing is number of widgets sold last year versus widgets sold this year; in my opinion, a scatterplot would be messy overkill for something this straightforward, but I do appreciate the suggestions. I tried putting a subset of the data I am graphing in my question but on preview, the tables were horribly bungled so I gave up on that :\
posted by Ziggy Zaga at 5:05 PM on October 15, 2006