Need help creating a line graph immediately.

[FrankSpe4]

Greetings,

I've never used Excel before, but must now form a line graph in Excel for a class project. I don't know how to combine 2 line graphs on one chart for comparison. Can anyone help me?

The first data set is motor production numbers for consecutive years from 1947 - 1989, but we are also being required to create a second time line showing a four-year running average for this time period. I realize that the averages would appear at the median of each set in the second line (ex. 2.5 for years 1-4, 6.5 for years 5-8), but I just don't know how to create one graph that shows both time lines.

Thanks for any help!

 

[awheeler]

Line graphs

In step 2 of chart wizard click on the 'series' tab and then click on the 'add' button, give it a name, then enter the range for your data in the 'Values' window. Hope that helps.

 

[SALBOL1]

go to the chart options (little thing looks like a graph) and select Line Chart....put your data on your worksheet and your various ranges will be seperated by commas. That should allow you to put various ranges on a chart, (Years, Running Average, Actual Values). Excel also has built in averaging, running average is probably on of them.

 

[Jon Peltier]

You can add a moving average, it's one of the Trendline options (Chart menu > Add Trendline). It doesn't give you one average for each N points, though, it gives you an average at each point for the previous N points. So at point 4 it would average points 1-4, at point 5, it would average 2-5, etc. I would presume this is the moving average you want, since this is very common, and I've never seen the other type used on purpose.

To get the average you want, however, you can simply do the math in the worksheet as follows.

I'd suggest doing your data like this:

1947    100    1948.5    113.25
1948    120
1949    115
1950    118
1951    125    1952.5    127.5
etc.

Suppose the data starts in A1, cell C1 would have this formula:

=average(A1:A4)

Copy C1, select D1 and paste, and D1 assumes this formula:

=average(B1:B4)

Copy C1:D1, select C5 and paste, select C9 and paste, etc, every four rows to the end.

The chart: Select columns A and B and create an XY chart using the chart wizard. Select and copy columns C and D, select the chart, and use Paste Special from the Edit menu, and add a new series, by columns, with category values in the first column. If all you get is points without connecting lines, select the chart, go to Options on the Tools menu, click on the Chart tab, and choose Interpolate for how you want Excel to treat blank cells.