Excel data analysis inconsistency

Question

I am having an issue that I am hoping somebody could shed some light on.

It involves inconsistency with the excel data analysis plug in regression function and the built in line of best fit function.

The intercept value I get from the graph plot varies from that supplied by the plug in.

It is only small but has a rather large effect as I need to use the reciprocal of that value in order to continue my analysis of the data.

If anybody had any pointers as to how to prevent this or an explanation as to why this may occur that'd be great

Answer 1

yeah, that is my bad, Thanks for picking up on that. I didn't look at the label for the box I was using for the add in. I made the (rather silly) assumption that the first box was inputs and the second was outputs. Probably should read things in future.

Answer 2

You've got your x's and y's mixed up.  If you use the same values for x's and y's in both the graph and the plug-in, you will get the same results.  If you do not, you get the results you see.

In generating the line chart, the X-axis will be the first column (1/S*) and the Y-axis the second (1/S). If you just select that range, and then activate the plug-in, without editing the range, those will be reversed.

Answer 3

I am working from raw data trying to plot the best line fit: using the add trend line I get 

y = -1.0294x + 0.0329

however if I use the linear regression portion of the data analysis plug in I get values that indicate

y = -0.9678x + 0.0318

I admit that these values don't vary by very much, but it means that I can't justifiably use the standard error in the intercept without atleast deciding which best fit represents the data more accurately 

Here is a link to the output of the data analysis plug in and the graph the inconsistent points are highlighted in yellow

https://onedrive.live.com/redir?resid=1EC8C3FB2CE9F019!107&authkey=!AOQCt-o0bNmW8F0&ithint=file%2cpdf

The underlying data for this is as follows 

1/S'	1/S
-0.06667	0.1
-0.0303	0.066667
-0.01511	0.05
0.008621	0.02
0.014771	0.016667
0.018692	0.014286
0.020367	0.0125
0.021834	0.011111
0.022727	0.01

Answer 4

What kind of variation are you seeing?  Could your problem be as simple as not using the graph plot derived value to it's maximum precision?  If that is not the case, provide data, formulas and procedures to allow someone to duplicate exactly what you are doing.