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 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 expression looks like LINEST(I2:I126,D2:H126,FALSE,TRUE) This is what is needed to perform multiple linear regression with LINEST. Each data point can then be identified by means of a second index $j$ as the ordered pair $x_-\alpha$.
There are five groups of data: let's index them by $k$ ranging from $1$ to $5$ (from bottom to top in the plot). We can figure this out by means of a mathematical expression for the implicit model. The fiddly part involves setting up the data in the right way. 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. Use Solver to find the x-intercept minimizing the mean squared residual. One of the outputs of this function is the mean squared residual.
#EXCEL LINEAR REGRESSION FOR PLOT TRIAL#
Perhaps the simplest uses LINEST to fit the lines conditional on a trial value of the x-intercept. There are several straightforward ways to do this in Excel. Which, aside from one really wonky point, converges pretty well. Which gives: Slope (m) Intercept (b) Common x-intercept (assuming their is one) If I assume that my data sets share an x-intercept, then I can find that x-value through: y = mx + b Plotting slope against y-intercept you see some kind of correlation: 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) 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.ĭoes such a linear regression analysis method exist? Does it have a name? Does it exist in Excel? In fact, it might even be: the x-intercept itself.
But then, in a flash, I realized that data might have a common point: I was about to perform 5 separate linear regressions, so I could get the slope and y-intercept of each "independent" data set.
I was plotting some linear data sets in Excel, including linear trend-lines: