forced linear regression

[Ed]

Hello,

I have a dataset with a (straight) line fit to it
(with regression value and everything). My question is how
do I take line with a different slope and force fit it to
my data and optimise this new fit (i.e. get a new,
obviously smaller, regression value that's a maximum for
the forced slope with my data)? It's sort of the opposite
of forcing a line through zero, I think. Anyway, is it
possible, and if so where do I go to find out how to do it?

 

[cmoore]

I think you have to do it the old fashioned way. Attached is a
example.

If you don't have 'Solver...' on your Tools menu, goto Tools/Add-in
and place a check in the Solver box.

HT

+----------------------------------------------------------------
| Attachment filename: slope.xls
|Download attachment: http://www.excelforum.com/attachment.php?postid=359387
+----------------------------------------------------------------

 

[Jerry W. Lewis]

If I understand your question, you are fitting the model
y(i) = a + b*x(i) + e(i)
where you know the x's, y's, and b; a is the unknown intercept, and the
e's represent lack of fit.

This model can be simplified to
[y(i) - b*x(i)] = a + e(i)
where the left side is entirely known. Clearly the least squares fit
for a is then
=AVERAGE(y_range - b*x_range)
which must be array entered (Ctrl-Shift-Enter)

Jerry