Trendlines

[Charles]

I am trying to draw a trendline for a data series which
used a secondary y-axis. It's looks as though the setup
for the trendline is working okay, but the actual
trendline does not show up on the chart. What might I be
doing wrong? Thank you

 

[Jon Peltier]

Charles -

Does the trendline appear in the legend? Does it appear in the chart
element selection dropdown box on the Chart command bar? Can you select
the series, then press the up arrow one or more times until it's selected?

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

 

[Guest]

Jon,

-----Original Message-----
Charles -

Does the trendline appear in the legend? Does it appear in the chart
element selection dropdown box on the Chart command bar? Can you select
the series, then press the up arrow one or more times until it's selected?

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


.

 

[Guest]

Jon, Thanks for answering my post. Yes, the trendline
does appear both in the legend and in the element
selection dropdown box on the Chart command bar. I
still ,however, have not been able to make it appear on
the chart itself. There is another trendline for another
data series which DOES appear. Can there only be 1
trendline per chart? Thank you.

Charles Pearce
Michelin North America

 

[Jon Peltier]

Charles -

You can have more than one trendline in a chart. In fact, each series
can have more than one trendline. I can't think of a reason that your
trendline isn't visible.

If it's not a large file, send me a copy, and I'll have a peek.

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

 

[Jon Peltier]

Charlie sent me his workbook, and after fiddling around for a bit, I
discovered the problem. As suspected, it was something stupid. Not
something Charlie has done, just a funny way Excel works.

When you make a line chart, Excel assigns category (X axis) labels to
the first series, then leaves these categories out of the other series.
The first series have the labels explicitly defined, as in this series
formula:

=SERIES(Totals!$C$4,Totals!$A$5:$A$49,Totals!$C$5:$C$49,1)

Subsequent series do not have it defined, shown by the missing argument
here:

=SERIES(Totals!$D$4,,Totals!$D$5:$D$49,3)

Excel by default uses the primary X values for the secondary series,
until a secondary X axis is added. When I copied the X Values range,
Totals!$A$5:$A$49, from the first formula, and pasted it into the second:

=SERIES(Totals!$D$4,Totals!$A$5:$A$49,Totals!$D$5:$D$49,3)

the missing trendlines magically appeared. The Source Data dialog showed
this X Values range, but these are the primary X values, and the
secondary X values ox was blank. Even though Excel uses the same X
values to plot the secondary series, they were ignored when determining
the trendline.

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