Excel 2007 : best fit line without outliers

**bored** · 11-23-2010, 01:45 AM

hi,

i have a scatter plot for which i want to insert a best fit line. however, there are some outliers within the data. i have manually identified these outliers and coloured them differently from the non-outliers, but what i would like to do now is to insert a best-fit straight line for the remaining good data, while still keeping the outliers on the scatter plot. is there a way to do that?

i found this post:
http://thedailyreviewer.com/office/v...iers-110140914
but do not understand how to "fit the trend line based on the new set of points, but graph the old set".

thanks for any help.

**JulieM** · 11-23-2010, 05:03 AM

He means duplicate all the data you want on the graph in your spreadsheet, in an added column to the table, with the exception of the points you wish to exclude.
Then when you format your chart area, make a scatter plot of both sets of data, but only add a trendline for the duplicated set.

Pottentially statistically dodgy. If there is a valid reason for considering the excluded points to be invalid, then don't include them in your data report at all. If the results don't fit the hypothesis, the hypothesis needs changing, not the other way around

**bored** · 11-23-2010, 05:37 AM

i see, now i get what he means. but is there a way to simply plot 1 set of data and then somehow tell excel that so and so points are not to be considered when fitting a line, while at the same time keeping them on the scatter plot?

i do realise that identifying 'outliers' may not be the best way to test a hypothesis, but i think it does make sense in cases where a very small minority of data points lies far away from a large majority and distorts an otherwise decent fit, especially when the experimental setup is vulnerable to random errors.