Linear Regression by set Slope (not intercept)

B

Ben

Hi Guys
is there a way in excel to get the linear regression function to
specify a function based on a set intercept (say =1), instead of a set
intercept.

I believe this ability should have been included in excel from day 1.
 
V

vandenberg p

Hello:

First I am not sure you could call it linear regression when you
are done. Forcing the intercept to a particular value is not the
same as solving the normal equations for the slope and intercept.

Having said that there are couple of ways to do it:

1. Graph the data and right click the series and add trendline,
there is an option to set the intercept to any value you want.

2. Compute the sum of error square form some arbitrary slope
coefficient and intercept of one using your X data. Now compute
the error from the actual Y and square it. Then sum it. Do this
with formulas and be sure that the slope is a variable in a cell.
Then user solver to minimize the error sum of squares by vary
the slope value. (You could then also compute the R^2 etc.)

As an example using the following data:

y x
10 15
14 34
21 25
49 75
53 61

I got an equation of Y = 1 + .69142X from both processes. The
trendline threw in the R^2 as .8578


Pieter Vandenberg





: Hi Guys
: is there a way in excel to get the linear regression function to
: specify a function based on a set intercept (say =1), instead of a set
: intercept.

: I believe this ability should have been included in excel from day 1.
 
J

Jerry W. Lewis

Since you did not proofread your note, your question is less than clear.

If you want to force a regression through an intercept of 1 (as you note
says), then subtract 1 from all y-values and force the intercept through 0.

If you want to force a regression to have a slope of 1 (as your subject
implies), then use AVERAGE(yData-xData) for the intercept. This is an array
formula that must be array entered (Ctrl-Shift-Enter).

Jerry
 
B

Ben

Jerry, i do not understand the usage of average(Y-X).
As Y-X is just a value, taking the average of it does nothing.

What i intent on doing is fitting lines with slope=1 to a small set of
close values.

Could you please elaborate
 
D

David Biddulph

Ben said:
Jerry, i do not understand the usage of average(Y-X).
As Y-X is just a value, taking the average of it does nothing.

Assuming that you have more than one data point, then you presumably have
more than one value for Y-X to average?

That is why Jerry said:
"If you want to force a regression to have a slope of 1 (as your subject
implies), then use AVERAGE(yData-xData) for the intercept. This is an array
formula that must be array entered (Ctrl-Shift-Enter)."
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top