Re: showing trend for non-existent data

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
_______