showing trend for non-existent data

Hi,

I am plotting a graph against time; i.e. x-axis is dates.

I am plotting a chart on year-to-year data, thus I have
data for 2003 which is plotted. In January 2004 I start
with a compeletely new set of data.

Thus, my source of data is 12 cells (one each for the 12
months). Obviously, I do not have data for upcoming
months.

When I plot the chart, the data for the remaining months
is considered as zeros and the line graph suddenly jumps
down to a value of zero.

Visually this does not look very sexy. Is there any means
to still mention to xls that the input source data is 12
cells but in the case where the value is not filled in, on
the chart displayed the trend is extrapolated in the same
direction as for the previous month(s)?

THank you.

John Parrot wrote:
When I plot the chart, the data for the remaining months
is considered as zeros and the line graph suddenly jumps
down to a value of zero.

You can tell Excel to *not* plot empty cells. Tools | Options | Chart tab.

Visually this does not look very sexy. Is there any means
to still mention to xls that the input source data is 12
cells but in the case where the value is not filled in, on
the chart displayed the trend is extrapolated in the same
direction as for the previous month(s)?

You have several options. Excel help does a pretty good job of describing
these options in a section entitled "About projecting values." Sounds to me
like you might want a trendline on your chart. Look up the key word
trendline in Excel help.

Dave
dvt at psu dot edu

When I plot the chart, the data for the remaining months
is considered as zeros and the line graph suddenly jumps
down to a value of zero.

You can tell Excel to *not* plot empty cells. Tools | Options | Chart tab.

Visually this does not look very sexy. Is there any means
to still mention to xls that the input source data is 12
cells but in the case where the value is not filled in, on
the chart displayed the trend is extrapolated in the same
direction as for the previous month(s)?

You have several options. Excel help does a pretty good job of describing
these options in a section entitled "About projecting values." Sounds to me
like you might want a trendline on your chart. Look up the key word
trendline in Excel help.

If the cells look like blanks, but result from formulas which return "",
change "" to NA() in the formulas. Then use conditional formatting in
the worksheet to hide the resulting #N/A errors. They are ugly in the
sheet, but are not plotted, and won't affect the trendline.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______