5
$\begingroup$

I was plotting some linear data sets in Excel, including linear trend-lines:

enter image description here

I was about to perform 5 separate linear regressions, so I could get the slope and y-intercept of each "independent" data set. But then, in a flash, I realized that data might have a common point:

enter image description here

In fact, it might even be: the x-intercept itself.

So what I need now is way to run multiple simultaneous linear regressions, with the assumption that all of the lines intersect at a common point.

Does such a linear regression analysis method exist? Does it have a name? Does it exist in Excel?


The best I've been able to muster so far is to run five independent linear regressions, getting the slope and intercept of each data set:

Slope (m)  Intercept (b)
=========  =============
 1.15287     11484.8  
 0.86301      7173.5
 0.43212      4306.4
 0.25894      2853.6

Plotting slope against y-intercept you see some kind of correlation:

enter image description here

If I assume that my data sets share an x-intercept, then I can find that x-value through:

 y = mx + b
 0 = mx + b
-b = mx
 x = m / -b

Which gives:

Slope (m)  Intercept (b)  Common x-intercept (assuming their is one)
=========  =============  ============================
 1.15287     11484.8        -9961.9
 0.86301      7173.5        -8312.2
 0.43212      4306.4        -9965.6
 0.25894      2853.6       -11020.2

Which, aside from one really wonky point, converges pretty well.

$\endgroup$
  • 1
    $\begingroup$ Regression involves fitting a linear model to real data which includes some random variation. Are you really interested in fitting regression lines or just ploting lines like your graphs seem to indicate? There is no point to fitting regression to data without a noise component. $\endgroup$ – Michael Chernick Jul 21 '12 at 15:19
  • $\begingroup$ i am looking to find parameters for a theoretical model that best fit real world data. If you'd like i can edit the images in the question; adding just enough noise to make it look "more random", without changing my question at all. $\endgroup$ – Ian Boyd Jul 21 '12 at 15:50
5
$\begingroup$

There are several straightforward ways to do this in Excel.

Perhaps the simplest uses LINEST to fit the lines conditional on a trial value of the x-intercept. One of the outputs of this function is the mean squared residual. Use Solver to find the x-intercept minimizing the mean squared residual. If you take some care in controlling Solver--especially by constraining the x-intercept within reasonable bounds and giving it a good starting value--you ought to get excellent estimates.

The fiddly part involves setting up the data in the right way. We can figure this out by means of a mathematical expression for the implicit model. There are five groups of data: let's index them by $k$ ranging from $1$ to $5$ (from bottom to top in the plot). Each data point can then be identified by means of a second index $j$ as the ordered pair $x_{kj}, y_{kj}$. (It appears that $x_{kj} = x_{k'j}$ for any two indexes $k$ and $k'$, but this is not essential.) In these terms the model supposes there are five slopes $\beta_k$ and an x-intercept $\alpha$; that is, $y_{kj}$ should be closely approximated by $\beta_k (x_{kj}-\alpha)$. The combined LINEST/Solver solution minimizes the sum of squares of the discrepancies. Alternatively--this will come in handy for assessing confidence intervals--we can view the $y_{kj}$ as independent draws from normal distributions having a common unknown variance $\sigma^2$ and means $\beta_k(x_{kj}-\alpha)$.

This formulation, with five different coefficients and the proposed use of LINEST, suggests we should set up the data in an array where there is a separate column for each $k$ and these are immediately followed by a column for the $y_{kj}$.

I worked up an example using simulated data akin to those shown in the question. Here is what the data array looks like:

[B] [C] [D] [E] [F] [G] [H] [I]
k   x   1   2   3   4   5   y
-----------------------------------------------
355 7355    0   0   0   0   636
355 0   7355    0   0   0   3705
355 0   0   7355    0   0   6757
355 0   0   0   7355    0   9993
355 0   0   0   0   7355    13092
429 7429    0   0   0   0   539
...

The strange values 7355, 7429, etc, as well as all the zeros, are produced by formulas. The one in cell D3, for instance, is

=IF($B2=D$1, $C2-Alpha, 0)

Here, Alpha is a named cell containing the intercept (currently set to -7000). This formula, when pasted down the full extent of the columns headed "1" through "5", puts a zero in each cell except when the value of $k$ (shown in the leftmost column) corresponds to the column heading, where it puts the difference $x_{kj}-\alpha$. This is what is needed to perform multiple linear regression with LINEST. The expression looks like

LINEST(I2:I126,D2:H126,FALSE,TRUE)

Range I2:I126 is the column of y-values; range D2:H126 comprises the five computed columns; FALSE stipulates that the y-intercept is forced to $0$; and TRUE asks for extended statistics. The formula's output occupies a range of 6 rows by 5 columns, of which the first three rows might look like

1.296   0.986   0.678   0.371   0.062
0.001   0.001   0.001   0.001   0.001
1.000   51.199
...     

Strangely (you have to put up with the bizarre when doing stats in Excel :-), the output columns correspond to the input columns in reverse order: thus, 1.296 is the estimated coefficient for column H (corresponding to $k=5$, which we have named $\beta_5$) while 0.062 is the estimated coefficient for column D (corresponding to $k=1$, which we have named $\beta_1$).

Notice, in particular, the 51.199 in row 3, column 2 of the LINEST output: this is the mean sum of squares of residuals. That's what we would like to minimize. In my spreadsheet this value sits at cell U9. In eyeballing the plots, I figured the x-intercept was surely between $-20000$ and $0$. Here's the corresponding Solver dialog to minimize U9 by varying $\alpha$, named XIntercept in this sheet:

Solver dialog

It returned a reasonable result almost instantly. To see how it can perform, compare the parameters as set in the simulation against the estimates obtained in this fashion:

Parameter Value   Estimate
Alpha     -10000  -9696.2
Beta1        .05    .0619
Beta2        .35    .3710
Beta3        .65    .6772
Beta4        .95    .9853
Beta5       1.25   1.2957
Sigma      50     51.199

Using these parameters, the fit is excellent:

Scatter plot with linear fits

One can go further by computing the fit and using that to calculate the log likelihood. Solver can modify a set of parameters (initalized to the LINEST estimates) one parameter at time to attain any desired value of the log likelihood less than the maximum value. In the usual way--by reducing the log likelihood by a quantile of a $\chi^2$ distribution--you can obtain confidence intervals for each parameter. In fact, if you want--this is an excellent way to learn how the maximum likelihood machinery works--you can skip the LINEST approach altogether and use Solver to maximize the log likelihood. However, using Solver in this "naked" way--without knowing in advance approximately what the parameter estimates should be--is risky. Solver will readily stop at a (poor) local maximum. The combination of an initial estimate, such as that afforded by guessing at $\alpha$ and applying LINEST, along with a quick application of Solver to polish these results, is much more reliable and tends to work well.

$\endgroup$
4
$\begingroup$

It is very unlikely that Excel would be able to do this easily or reliably (you should really not use Excel for any but the simplest stats, and sometimes not even then).

If you know (or think you know) what the common x intercept is (not just estimate it from the data) then you can subtract that value from all the x variables and do a regression without intercept (because the line should go through 0,0 now). You can compare that model to the model with each line having its own intercept (if they all go through the same fixed intercept then all the fitted intercepts should simultaneously be not significantly different from 0.

A quick way to get a feel for if your x intercepts are likely to be the same would be to reverse your x and y variables and fit the lines, this means that now the y-intercepts would be the same which is easier to test. However this also changes the direction of the error and so answers a bit of a different question and should probably be followed up by something more formal.

You could create bootstrap estimates of the x intercept (computed as -b/m) and use that to estimate if the intercepts differ.

You could fit a nonlinear least squares model to estimate the model with a common x-intercept and compare it with a model where each gets its own intercept to see if they are significantly different (the model would be of the form slope*(x-x0) with slope and x0 as the parameters (x0 being the x-intercept).

You could fit the similar model using Bayesian techniques as well to compare.

Any of these would be doable in R or other statistical packages.

$\endgroup$
0
$\begingroup$

I'm only a chemist not a statistician but the easiest way I know is to use dummy variables. i.e. for each (n-1)slope assign a 1 in the z column and multiply x*z. This means one slope will have 0 in all z's. For a common intercept with 3 slopes it would look like this.

batch   y   x   xz 48   xz 58
48          0.9 0   0   0
48          0.7 12  12  0
48          0.6 24  24  0
48          0.6 36  36  0
48          0.66    48  48  0
48          0.59    60  60  0
58          1   0   0   0
58          0.9 12  0   12
58          0.8 24  0   24
58          0.75    36  0   36
58          0.82    48  0   48
69          1   0   0   0
69          0.9 12  0   0
69          0.84    24  0   0
69          0.83    36  0   0

Then use the Excel regression add in. SUMMARY OUTPUT

Regression Statistics                       
Multiple R  0.838                   
R Square    0.703                   
Adjusted R Square   0.622                   
Standard Error  0.0851                  
Observations    15                  

ANOVA                       
    df  SS  MS  F   Significance F  
Regression  3   0.1886  0.0629  8.68    0.003   
Residual    11  0.0797  0.00725         
Total   14  0.2683              

    Coefficients    Standard Error  t Stat  P-value Lower 95%   Upper 95%
Intercept    0.901          0.0381   23.7   9.E-11   0.817          0.984
months  -0.00199    0.00233 -0.85   0.41    -0.00712    0.00315
xz 48   -0.00441    0.00218 -2.02   0.068   -0.00921    0.00039
xz 58   -0.000725   0.00232 -0.31   0.76    -0.00582    0.00437

The ANOVA is rubbish but the output will give you all the equations for each of the lines.e.g.

Y=0.901-0.00199x for b69
Y=0.901-0.00199x-0.00218zx for b48
Y=0.901-0.00199x-0.000725zx for b58
$\endgroup$
  • $\begingroup$ Please edit to fix formatting of your data and output, tabs don't work great here but spaces line everything up fine. How does this answer the initial question and add to the answers already given? $\endgroup$ – John Sep 23 '13 at 11:04
  • $\begingroup$ @John It has the virtue of being slightly more understandable than the accepted answer. You'll note i accepted whuber's answer, without being to understand it, or use it. But it looks like he put a lot of work into it; so it must be right. But i still don't know how to simultaneous linear regressions. More answers can help me, and anyone else, with that. $\endgroup$ – Ian Boyd Sep 23 '13 at 14:20
  • $\begingroup$ @IanBoyd I am not sure it is good practice to accept an answer you do not understand and feel you cannot use - that is not a comment on whuber's answer since I always find them of high quality, but only on how to select an answer! $\endgroup$ – kirk Sep 23 '13 at 15:21
  • $\begingroup$ @kirk Presumably the people who hardcore stats guys would all agree (given the majority of upvotes) that whuber's answer is correct and should be accepted. But as a wiki-style site, stats can always benefit from more techniques and approaches. $\endgroup$ – Ian Boyd Sep 23 '13 at 17:14
  • $\begingroup$ That's all well and good but this answer is about data that converges on the y, not the x, and it's intentionally constraining the intercepts to be equal and finding a poor fit. The fitted lines in this graph do not intercept x at the same location. Perhaps it was implicit that the data is rotated to so that x and y are reversed but that's really a very different kind of an answer and doesn't directly address the question (as Greg Snow's answer suggests). Any answer that requires flipping the x and y needs to address the potential issues with that as well. $\endgroup$ – John Sep 23 '13 at 17:30

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.