LINEST

Returns a table of statistics for a straight line that best fits a data set.

Syntax:

LINEST(yvalues, xvalues, allow_const, stats)

yvalues is a single row or column range specifying the y coordinates in a set of data points.

xvalues is a corresponding single row or column range specifying the x coordinates. If xvalues is omitted it defaults to 1, 2, 3, ..., n. If there is more than one set of variables xvalues may be a range with corresponding multiple rows or columns.

LINEST finds a straight line $y=a+bx$ that best fits the data, using linear regression (the "least squares" method). With more than one set of variables the straight line is of the form $y=a+b_1{x}_1+b_2{x}_2\cdots +b_n{x}_n$.

if allow_const is FALSE the straight line found is forced to pass through the origin (the constant a is zero; $y=bx$). If omitted, allow_const defaults to TRUE (the line is not forced through the origin).

$b_1$  to $b_n$  are the line gradients; a is the y-axis intercept.

${\sigma }_1$ to ${\sigma }_1$ are the standard error values for the line gradients; ${\sigma }_a$ is the standard error value for the y-axis intercept.

$r^2$ is the determination coefficient RSQ; ${\sigma }_y$ is the standard error value for the y estimate.

F is the F statistic (F-observed value); df is the number of degrees of freedom.

$s{s}_{reg}$ is the regression sum of squares; $s{s}_{resid}$ is the residual sum of squares.

Example:

In the example above, cells A2:B8 contain the x,y values for a set of points. LINEST(B2:B8,A2:A8,1,1) returns the statistics for the best fit line through those points.

In the example above, you measure the floor area and count the windows of a sample of houses in the area, and make a table with the corresponding sale value (cells A2:C8). To predict the value of other houses in the area you might use: value = a + b1*floor_area + b22*num_windows, where a, b1 and b2 are constants. LINEST(A2:A8,B2:C8,1,1) returns appropriate statistics for that equation.

Interpretation:

• Slope (m): The slope is approximately 175.76. This means that for every additional square foot of a house, the selling price is predicted to increase by about $175.76.
• Y-intercept (b): The y-intercept is approximately 114,142.42. This is the predicted price of a house with zero square footage. While this value doesn't make sense in the real world, it's a necessary component of the linear regression equation.
• Equation of the line of best fit: Based on these values, the equation is:
• • y=175.76x+114,142.42
  • (Selling Price) = 175.76 * (Square Footage) + 114,142.42
• Coefficient of Determination (R2): The R2 value is 0.98. This is a very high value, indicating that 98% of the variation in house prices can be explained by the variation in square footage. This suggests a very strong linear relationship between the two variables, and our model is a good fit for the data.
• Standard Error of the Slope and Y-intercept: These values give you an idea of the precision of your calculated slope and y-intercept. Lower values indicate more reliable estimates.

Conclusion:

Using the LINEST function, you have successfully created a predictive model for house prices in this neighborhood. You can now use the equation y=175.76x+114,142.42 to estimate the price of a house if you know its square footage. For example, a house of 2000 square feet would be estimated to sell for:

175.76∗2000+114,142.42=351,520+114,142.42=$465,662.42