Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
38N90W
 
Posts: n/a
Default Newbie to charts question - projecting values between data points

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-


  #2   Report Post  
Jon Peltier
 
Posts: n/a
Default

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 wrote:

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-



  #3   Report Post  
38N90W
 
Posts: n/a
Default


"Jon Peltier" wrote in message
...
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-


  #4   Report Post  
Jon Peltier
 
Posts: n/a
Default

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/
_______

38N90W wrote:

"Jon Peltier" wrote in message
...

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-



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional format of data tables in charts [email protected] Charts and Charting in Excel 2 January 25th 05 04:56 PM
space between y axis and data points.. Dave R. Charts and Charting in Excel 3 January 8th 05 05:56 AM
To get a lot of charts that contain different values in a minute Kevin Charts and Charting in Excel 0 December 28th 04 07:45 AM
Help with pivot charts and data labels [email protected] Charts and Charting in Excel 1 December 15th 04 04:08 PM
Extending a Chart Data Series from an Array - Can it be done? Jon Peltier Charts and Charting in Excel 4 November 30th 04 04:30 AM


All times are GMT +1. The time now is 08:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"