Trendlines in graphs

  • Thread starter Thread starter Brad Gover
  • Start date Start date
B

Brad Gover

Hi all! I have an interesting question. I am ploting a
graph where the X axis is by months and the Y axis is the
number of parts produced. Some months we do not produce
any parts; therefore, the Y axis is "0" at that time.
When I put a trend line on the chart, it sees the "0" as a
computable data point. A line is then generated but is
off from the true trend. The true trend can be generated
if I remove all the non producing months. Is there a way
to specify specific groups of cells in a column that you
want to use in a trendline analysis--sort of hop scotch
over cells? Hope to hear from you.
Your cyber buddy, Brad/
 
The chart trendline will be computed using all charted data points. If
you are giving an explicit zero, it shouldn't be surprising that Excel
uses it in the calculation. The surprising thing is that a formula like
=IF(formula=0,"",formula)
will still plot as zero and be used as a numeric zero in the trendline
calculation.

To omit the non-producing months from the graph, the cells for the
non-producing months should either be empty, or else should contain
#N/A. Either way, there would be no plot symbol for the point. There
would be a difference if in addition to the trendline, you want to
connect successive data points by straight lines; an empty cell would
cause a break in the connecting line, while #N/A would not.

The easiest way to plot a zero on the graph and ignore it in the
trendline, is to plot two data series: one with the zeros, and one
without. Do not connect data points on the series without zeros, but
plot the trendline for the series without zeros.

Jerry
 
Goto tool>options.
click the tab chart.
click the button "plot empty cells as:" Not Ploted
I think that should do it.
 
Back
Top