Linear Regression by set Slope (not intercept)

[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.

 

[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.

 

[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

 

[Ben]

ah yes of course i mean set 'slope'

 

[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

 

[David Biddulph]

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)."