Newbie to charts question - projecting values between data points

[38N90W]

Excel 2002 SP2
Windows XP Pro

I'm trying to make a line chart showing data points of two series.

My X-axis labels are years, from 1970 through 2010.
My Y-axis labels are population values.

The first series (Series1) comes from actual data values in a column on
another sheet.

The second series (Series2) are calculated values that are in a column next
to the values for the first series.

Concerning just the first series:

First of all, for some reason the data points are appearing on the chart but
the line between points is not.

Secondly, and perhaps explaining the above, not every year value has a
population value. In these cases the cell for that particular year is blank
and does not contain a zero or any other value.

My aim is twofold, 1) to simply make a chart showing at least the Series1
data points with lines connecting the points, and 2) to try to impose a sort
of trendline between the first and last points that *must* pass through each
data point. I've managed to put some trendlines on the chart using Excel's
built in Trendline option, but they don't necessarily include the data
points. Is there a way to force a line that passes through all the data
points? Perhaps a trendline is a poor choice of words, maybe more like a
projection line.

Anyway, any help, suggestions or links would be greatly appreciated.

Thanks!

-gk-


=================================================
The creative act is not the province of remote oracles or rarefied geniuses
but a transparent process that is open to everyone.
-Greg Kot in Wilco Learning How To Die-

 

[Jon Peltier]

Greg -

To make the lines appear (assuming the intervening cells are truly blank, and not
just formulas that appear blank to the human eye), select the chart, go to Options
on the Tools menu, and on the Chart tab, choose the Show Empty Cells As --
Interpolate option.

A trendline will not pass through all points of any but the most mundane data sets.
You can make it extend beyond the data: double click the trendline, click on the
Options tab, and choose the number of units to forecast forward or backward
(back-cast?). You could use a smoothed line to make it look like a "best fit through
all points" curve.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[38N90W]

Greg -

To make the lines appear (assuming the intervening cells are truly blank,
and not just formulas that appear blank to the human eye), select the
chart, go to Options on the Tools menu, and on the Chart tab, choose the
Show Empty Cells As --
Interpolate option.

A trendline will not pass through all points of any but the most mundane
data sets. You can make it extend beyond the data: double click the
trendline, click on the Options tab, and choose the number of units to
forecast forward or backward (back-cast?). You could use a smoothed line
to make it look like a "best fit through all points" curve.

- Jon

Thanks Jon!!

Are there any AddIns for doing Chart analysis?

-gk-

 

[Jon Peltier]

Greg -

What kind of analysis are you looking for?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______