MS Excel. XY scatter graph - Linear regression trend line.

[scruggs]

Hi guys, hope you don't mind me posting this here, just seems the type of question could answer...

I've plotted an XY scatter graph in MS excel, and added a linear regression trend line. How can I now calculate the difference between the data points I've entered and the linear regression line?

I'm trying to work out error between each XY data point and the linear regression trend line.

Thanks all.

S.

[FTE Pruner]

You can do it manually by taking the equation of the line (which is a display option within the excel line plot options) and then generate another set of y data using your data set x values and then do a simple calculation to work out the delta between your data set and your new idealised data set.

This would give you an error for each value.

Is this the sort of thing you are after?

Pruner.

[OverRun]

FTE Pruner has a nice solution which goes to the heart of the matter in statistical terms, and this made me think of the more detailed statistical functions in Excel which can deliver the needed answers.

These are not enabled as a default, and need to be enabled (but worth doing). My own copy of Excel was disabled (thanks to those lovely folks at Microsoft who issue all these updates with disabling powers), so I had to do it from scratch.

Start Excel; top menu: TOOLS ADDINS and then enable the ‘analysis’ ones. On my version, the screen blinked a few times, and then they were working. Then TOOLS DATA ANALYSIS and choose the regression option. Pick the ‘X’ values and the ‘Y’ values, and (before the analysis) I ticked all the options. In the resultant new screen or printout, the “residual options” gave me the predicted values and the error (the so-called ‘residuals’). Very neat.

A word of warning to the serious scholars, Excel is good in this sort of simple analysis (and there are lots more statistical methods in the ADDINS toolpak), and these would satisfy many test pilots/engineer needs. However, they have limitations for serious statistical analysis, and if you are doing a Masters or PhD, or a flight test programme with more than 100 datasets, then my experience says invest in SPSS. Copies can be purchased from eBay at very good prices, and Julie Pallant's book (from Amazon) helps you gets answers straightaway.

[scruggs]

Cheers guys!

S

[Matthew Parsons]

Rather than copying the result from the graph, use the LINEST() function. It will give you m and b from y=mx+b. That way if your data changes, then you don't have to enter the coefficients manually from the graph.