Linear fit while fixing slope

[fermiona81]

Version: 2008 Processor: Intel Hello,
If a I have a set of x values and y values and I want to fit them with a line such that I fix the slope value to a certain number and fit the data to get the y-intercept. How can I do that in Excel?

thanks

 

[Mike Middleton]

fermiona81 -

One way is to use Solver to search for the y-intercept.

You can get Solver for Mac Excel 2008 from http://www.solver.com/mac/

Set up a worksheet to have a "changing cell" containing the y-intercept.

With x and y data in columns, create a formula in another column that
calculates the fitted y value, dependent on the slope value, y-intercept
changing cell, and the x data.

Use another column for residuals (actual y minus fitted y) and another
column for squared residuals.

Sum the squared residuals, which is your "target cell."

Use Solver to minimize the target cell by changing the y-intercept cell.

- Mike
http://www.MikeMiddleton.com
(e-mail address removed)


Version: 2008 Processor: Intel
Hello,
If a I have a set of x values and y values and I want to fit them with a
line such that I fix the slope value to a certain number and fit the data to
get the y-intercept. How can I do that in Excel?
thanks