Selective Trendlines

[JaysonG]

Hi I was wondering is it possible to create a linear trendline on an X
scatter graph but not include anomalous results?

In effect picking the points you want your line to be based on?

Thanks
Ja

 

[Jerry W. Lewis]

Not by interacting with the graph. You could add a helper column with
1's corresponding to the observations you want to include and blanks
corresponding to the observations you want to exclude.

=SLOPE(IF(INUMBER(include),ydata),xdata)

will then give the desired slope. Use INTERCEPT similarly. This
approach will not work for complicated models (polynomial regression,
etc.) since LINEST will not calculate unless all passed cells are numeric.

Jerry
Excel MVP

 

[Hardy]

I half answered it. Sorry, haven't got time to crack the last bit

The trend just does linear least squared regression of one series o
the other. You don't have to know what this means if it's not you
thing.

Sheet has chart with linear trend created from Chart menu. You als
have the option on this to show the equation on the chart.

You can also achieve this by using Tools-Data Analysis-Regression. Se
X and Y ranges as done in spreadsheet with output in Col G. Th
intercept and X1 variable coefficient are exactly the same as for tha
created through Chart ment.

So, in col F, I used equation to create points for trendline. Yo
could then plot this and it should sit exactly on top of the on
created on the chart (hope it does anyway).

This is the route by which you would do revised line. I have create
two more series, which link to Col A. Put X in col A and both value
are excluded.

Trouble is that "" or #N/A or whatever else in the range for Regressio
does not allow regression to be run.

I have not worked out way round this yet. You could create macro t
remove formulas and then remove spaces from the series.

Sorry not to have conclusion at this point - just busy

Attachment filename: trend.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=54466

 

[JaysonG]

ahh that just might do it... thanks

 

[Jerry W. Lewis]

"" is ignored by SLOPE, INTERCEPT, etc., but plots as zero.

#N/A is ignored in charting, but propagates through worksheet formulas.
=SLOPE(IF(ISNA(ydata),,ydata),xdata)
would still calculate.

LINEST will not tolerate any non-numeric data of any kind.

Jerry