CODEWITHSUNDEEP
excel
Brian
Brian

Reputation: 27392

Custom regression analysis in excel

I have a table in excel. The first column is the values for the independent variable. The other columns are sets of data for the dependent variable. I want to fit each data set to the langmuir equation: y=Ax/(1+Bx). Is there a way to write a function that will find A and B for each column?

Upvotes: 2

Views: 4767

Answers (1)

Excellll
Excellll

Reputation: 5785

You can do this in Excel with the Solver plug-in. You'll need to add the following to your sheet:

  1. Two cells that contain your changing A and B coefficients for the regression;
  2. A column of length equal to that of your dependent variable data. Fill down this formula in the column: =$F$2*A2/(1+$F$3*A2), where F2 and F3 are your A and B coefficients, and A2 is the first independent variable value.
  3. Another cell that contains the Residual for your regression. I used the RMSE with this array formula: {=SQRT(AVERAGE((B2:B28-C2:C28)^2))} (Enter the formula by pressing Ctrl+Shift+Enter.) Here B2:B28 is the dependent variable data you are fitting, and C2:C28 is the data for the regression function.

Now you are set up to use Solver to fit your regression function. Set Solver to minimize the residual while changing the A and B coefficients.

You should be able to handle all your data sets programmatically from here on out with a procedure similar to the following:

  1. Make sure regression column matches length of your data.
  2. Change residual formula to match present data of interest.
  3. Set seed values for A and B.
  4. Run solver.
  5. Record coefficient values and residual somewhere else.
  6. Repeat.

Upvotes: 3

Related Questions