Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linear Regression by set Slope (not intercept)
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linear Regression by set Slope (not intercept)
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linear Regression by set Slope (not intercept)
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" 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linear Regression by set Slope (not intercept)
ah yes of course i mean set 'slope'
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linear Regression by set Slope (not intercept)
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Linear Regression by set Slope (not intercept)
"Ben" wrote in message
oups.com... 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)." -- David Biddulph |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Regression intercept to 0 | Excel Worksheet Functions | |||
how do i graph multiple linear regression? | Excel Discussion (Misc queries) | |||
how do i graph multiple linear regression? | Excel Discussion (Misc queries) | |||
how do i graph multiple linear regression? | Excel Discussion (Misc queries) | |||
What do those numbers mean when i perform linear regression on exc | Excel Worksheet Functions |