2
$\begingroup$

Is it possible to impose an identical slope during a multiple linear regression?

See below I have 3 sets of data with a linear fit y=ax+b. I would like to simultaneously impose the same a (while minimizing the error) and let excel return the b values for the 3 lines.

Thank you very much for any pointers.

enter image description here

$\endgroup$
  • $\begingroup$ Just to clarify, this is about three separate simple linear regressions, not about "multiple linear regression"; and you are trying to reuse the coefficient from set 1 (for instance) on set 2 and 3 and let excel find the best intercept? $\endgroup$ – GuillaumeL Apr 24 at 12:31
  • $\begingroup$ Yes it is 3 simple linear regressions but that would be optimized simulatenously: I am hoping excel can not only find the intercept (b value) but also return the slope (a value) that minimizes the error on all 3 data set simulatenously. I do not want to impose manually the slope, I would like the find that "optimized" value. $\endgroup$ – John Apr 24 at 13:17
2
$\begingroup$

This is something where you push Excel to its limits, you might consider using more advanced statistical tools (e.g. R). But the following procedure might do the trick:

  1. calculate the means of x- and y-values for all three data sets: $\bar{y}_i$, $\bar{x}_i$, $i=1,2,3$.
  2. standardize your three data sets by subtracting the x-mean from the x-values and the y-mean from the y-values. If you use the standardize formula from Excel, provide $1$ as standard deviation. This step centers your scatterplots around the origin of the plot (effectively forcing the intercept in the regression to be zero).
  3. now put the centered values from all three sets into one big set (concatenating all three sets of x-values and all three sets of y-values) into two new variables $x^{\ast}$ and $y^{\ast}$.
  4. do the linear regression for this large set $(x^{\ast}, y^{\ast})$: you get an intercept of zero (or very close to zero) and one (!) slope $m$ that should be the best fit (or very close to it?).
  5. the correct intercepts $c_i$ for the three original data sets are retrieved by solving $\bar{y}_i=\bar{x}_i\cdot m + c_i$ for $c_i$, where $\bar{y}_i$ and $\bar{x}_i$ are the calculated means for the data sets and $m$ is the fitted slope.

I'm not entirely sure if this all mathematically correct, but it should solve your problem with Excel's capability.

$\endgroup$
  • $\begingroup$ This is a nice work-around. However, Excel has a built-in function LINEST to perform multiple regression, which allows it to make easy work of this problem. $\endgroup$ – whuber Apr 24 at 14:47
  • $\begingroup$ How so? I guess one would need to specify additional variables which are constantly 1 in one group and 0 in another group to model the different intercepts. But I'm not familiar with LINEST (nor Excel). $\endgroup$ – Edgar Apr 24 at 14:51
  • 1
    $\begingroup$ Correct: you need to create dummy variables. But that's a little bit easier than calculating the three pairs of means for the three datasets. At the step where you write "do the linear regression," you are telling the OP to use LINEST anyway. Thus, the natural workflow in Excel is the same as in any other statistical application: create the variables you need and apply the OLS fitting procedure. $\endgroup$ – whuber Apr 24 at 14:57
  • $\begingroup$ Could you please elaborate on how to use LINEST to solve the issue faced please? thank you very much $\endgroup$ – John Apr 24 at 17:38
  • $\begingroup$ The usage of LINEST is =LINEST(A2:A20;B2:B20), where your y-values are in cells A2 to A20 and your x-values are in cells B2 to B20. $\endgroup$ – Edgar Apr 25 at 8:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.