Trendline formatting doesn't print or preview properly!?

  • Thread starter Thread starter Graham Wideman
  • Start date Start date
G

Graham Wideman

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
 
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
 
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 -

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/ExcelTips/Polynomial.htm

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

http://www.google.co.uk/[email protected]

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/
_______
 
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
 
Back
Top