Trendline formatting doesn't print or preview properly!?

Folks:

Bug? Known issue? Or Operator Error?

I notice that in an Excel Chart, if you set the line format of a trendline
to one of the dashed formats it has an oddly coarse appearance when viewed
in the worksheet window (dashed but messy), any when printed (or viewed in
Print View) the line style appears as continuous.

The same dashed line formats for regular data series view and print just
fine.

We've tried this on Excel 2002 and 3, on two different PCs with two entirely
different sets of data and excel docs and printing to three different brands
of printers.

Not sure if printer driver has any impact on any of this, but the fact that
regular series print fine probably eliminates that.

Any clues? Confirmation that you too see this? Thanks,

Graham

--
---------------------------------------------------
Graham Wideman
Microsoft Visio MVP
---------------------------------------------------
Book/Tools:
Visio 2003 Developer's Survival Pack
Resources for programmable diagramming at:
http://www.diagramantics.com

Partially answer!

Well, at least a better diagnosis:

Turns out that the symptom noted (dashed lines on trendlines print as
continuous lines) only occurs for curved trendlines (such as polynomial),
not linear.

It appears that what's happening is this:

Excel is indeed using the dashed line format, applying it to the dashed
lines between the points that it's calculating for the trendline.

But the higher the resolution of the picture (eg: printing, or zooming in)
the more finely does Excel calculate the points, And hence the shorter those
line segments... to the point where the the length between the points is
less than the length of even one dash.

Hence the appearance of a continuous line, albeit a bit messy in some views
as the adjacent initial dash segments somewhat jumble together.

OK, so this could be relieved if one could set the interval at which Excel
calculates the curve, but I don's see such a setting in the UI or a property
in the object model.

Alternatively, with some programming, if one had access to the trendline's
data series, one could set a pattern of alternating line-segment colors (or
say 3 white, 3 black, 3 white... if they are very fine). But I don't see a
way to get at the trendline's series either.

Any other ideas?

Graham
--
---------------------------------------------------
Graham Wideman
Microsoft Visio MVP
---------------------------------------------------
Book/Tools:
Visio 2003 Developer's Survival Pack
Resources for programmable diagramming at:
http://www.diagramantics.com
"Graham Wideman" <[email protected]> wrote in message
news:[email protected]...

> Folks:
>
> Bug? Known issue? Or Operator Error?
>
> I notice that in an Excel Chart, if you set the line format of a trendline
> to one of the dashed formats it has an oddly coarse appearance when viewed
> in the worksheet window (dashed but messy), any when printed (or viewed in
> Print View) the line style appears as continuous.
>
> The same dashed line formats for regular data series view and print just
> fine.
>
> We've tried this on Excel 2002 and 3, on two different PCs with two
> entirely different sets of data and excel docs and printing to three
> different brands of printers.
>
> Not sure if printer driver has any impact on any of this, but the fact
> that regular series print fine probably eliminates that.
>
> Any clues? Confirmation that you too see this? Thanks,
>
> Graham

Slightly improved answer:

(If I knew how much I'd turn out to know I should have called myself
earlier!)

The problem arises because Excel wants to recalc the trendline curve using
more-finely spaced points when you tell Excel to draw or print to
higher-resolution device. As a side-effect that obscures the dashed line
effect.

But we can take a copy of the chart image when it's in coarsely-calculated
state (eg: Excel worksheet window at 100%) and paste the image (vector
"Picture" not bitmap) somewhere else, like Word. That prints just fine
(because of coarse Word is not going to recalc the trendline).

Graham

--
---------------------------------------------------
Graham Wideman
Microsoft Visio MVP
---------------------------------------------------
Book/Tools:
Visio 2003 Developer's Survival Pack
Resources for programmable diagramming at:
http://www.diagramantics.com

Graham -

You could access the trendline formula using the LINEST worksheet
formula. Bernard Liengme shows how to do this for a polynomial fit,
which should help you figure it out for another fitted model:

http://www.stfx.ca/people/bliengme/E...Polynomial.htm

Or you could extract it directly from the trendline formula using Dave
Braden's parsing macro:

http://www.google.co.uk/groups?selm=....microsoft.com

Now put some X values in a column at the frequency you want, and use the
coefficients you've determined to calculate the corresponding Y values
in the next column, and put your own custom trendline on the chart.

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

Graham Wideman wrote:

> Partially answer!
>
> Well, at least a better diagnosis:
>
> Turns out that the symptom noted (dashed lines on trendlines print as
> continuous lines) only occurs for curved trendlines (such as polynomial),
> not linear.
>
> It appears that what's happening is this:
>
> Excel is indeed using the dashed line format, applying it to the dashed
> lines between the points that it's calculating for the trendline.
>
> But the higher the resolution of the picture (eg: printing, or zooming in)
> the more finely does Excel calculate the points, And hence the shorter those
> line segments... to the point where the the length between the points is
> less than the length of even one dash.
>
> Hence the appearance of a continuous line, albeit a bit messy in some views
> as the adjacent initial dash segments somewhat jumble together.
>
> OK, so this could be relieved if one could set the interval at which Excel
> calculates the curve, but I don's see such a setting in the UI or a property
> in the object model.
>
> Alternatively, with some programming, if one had access to the trendline's
> data series, one could set a pattern of alternating line-segment colors (or
> say 3 white, 3 black, 3 white... if they are very fine). But I don't see a
> way to get at the trendline's series either.
>
> Any other ideas?
>
> Graham

Jon:

Yes, that method occured to me too... so thanks for the two pointers which
might make life much easier. For right now I think the copy-paste method is
going to suffice, but in future...

Graham

--
---------------------------------------------------
Graham Wideman
Microsoft Visio MVP
---------------------------------------------------
Book/Tools:
Visio 2003 Developer's Survival Pack
Resources for programmable diagramming at:
http://www.diagramantics.com
"Jon Peltier" <[email protected]> wrote in message
news:[email protected]...
> Graham -
>
> You could access the trendline formula using the LINEST worksheet formula.
> Bernard Liengme shows how to do this for a polynomial fit, which should
> help you figure it out for another fitted model:
>
> http://www.stfx.ca/people/bliengme/E...Polynomial.htm
>
> Or you could extract it directly from the trendline formula using Dave
> Braden's parsing macro:
>
> http://www.google.co.uk/groups?selm=....microsoft.com
>
> Now put some X values in a column at the frequency you want, and use the
> coefficients you've determined to calculate the corresponding Y values in
> the next column, and put your own custom trendline on the chart.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>