software

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

yx
1015
1434
2125
4975
5361

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

Ben gt; 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.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

quot;Benquot; wrote:

gt; Hi Guys
gt; is there a way in excel to get the linear regression function to
gt; specify a function based on a set intercept (say =1), instead of a set
gt; intercept.
gt;
gt; I believe this ability should have been included in excel from day 1.
gt;
gt;

ah yes of course i mean set 'slope'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 elaboratequot;Benquot; gt; wrote in message oups.com...
gt; Jerry, i do not understand the usage of average(Y-X).
gt; 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:
quot;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).quot;
--
David Biddulph