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




Ben wrote:

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