Straight Line Through Most Common Points

[TedN]

Is there a Formula that given a number of points Excel will plot a straight line where the majority of points fit or nearly fit the line, i.e. excludes the extreme points.


Example:

X: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

Y: 93, 82, 80, 81, 68, 79, 78, 90, 77, 68


The straight line would be drawn as a best fit for 82, 80, 81, 79, 78, 77, ignoring the other values.


Thanks,

 

[Luke M]

Assuming your X data is in B2:K2, and Y data is in B3:K3, formula in B4

=IF(ABS(B4-AVERAGE($B$4:$K$4))>STDEV($B$4:$K$4)*1,NA(),B4)

Copy over to K4, add this as a series to your chart, and then add a trendline to that series.

Currently, formula uses a 1 * standard deviation as the cutoff mark. If you want to change that, modify the *1 portion of the formula to whatever you want.

 

[Montrey]

Just right click the line and Add trendline.

 

[TedN]

Thanks Luke, that works well. A small thing, I believe you had a typo in the formula, should be row 3 that's referred to, not row 4.


Montrey, the problem with going straight to a trend line is that it includes all points, even the extreme ones. For my use I needed a trend line that reflected the part of the chart with similar sloping values.


Thanks again.

 

[Luke M]

Doh, good catch TedN. Looks like I accidentally setup my page wrong when building the example. Glad to hear that it did not cause you too much trouble.