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

You could do this with Solver
Send me (my private email NOT newsgroup) a sample file
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Canary Pete" <Canary wrote in message
...
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

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

"Canary Pete" wrote:

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

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

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"Canary Pete" <Canary wrote in message
...
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

I used Solver and then thought about translation with LINEST. I got the same
results.
--
Bernard
www.stfx.ca/people/bliengme
remove CAPS in email address


"Jerry W. Lewis" wrote in message
...
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

"Canary Pete" wrote:

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