regression using set intercept

[Guest]

Please help

I can use the SLOPE and INTERCEPT formulas but I want to be able to
recalculate the 'best-fit' SLOPE using a defined intercept. I could do this
with the Regression data analysis tool but this seems very long winded when I
want to try out different intercepts and use them for different Y values.

Is there a simple formula I can use? TREND doesn't seem to do the job

many thanks in advance

Peter

 

[Bernard Liengme]

You could do this with Solver
Send me (my private email NOT newsgroup) a sample file
best wishes

 

[Guest]

If you want to estimate the slope of simple linear regression where the
intercept has been forced to be the constant (a), then use
=LINEST(ydata-a,xdata,FALSE)
Since you are only interested in a single value (slope), it is not necessary
to array enter this. This should be more numerically accurate than using
Solver.

If you are interested in the related statistics that LINEST can return, you
should be aware that for Excel versions prior to 2003, LINEST returns
incorrect values for R^2, F, and SSreg when the intercept is forced to zero.

Jerry

 

[Bernard Liengme]

I have a worksheet for you but your server will not accept any messages
(with/without attachments)

 

[Bernard Liengme]

I used Solver and then thought about translation with LINEST. I got the same
results.