Leading the way in Microsoft Office Development

LINEST(known_y's [,known_x's] [,const] [,stats])

The LINEST() function returns the coordinates for a straight line that best fits your data.

This function must be entered as an array formula using (Ctrl + Shift + Enter) because it returns a two-dimensional array.

To find out more about Array Formulas please refer to the Array Formulas section.

This function can be used to return the slope and the intercept values for the best fit line.

Alternatively you can also use the INTERCEPT() and SLOPE() functions to obtain the same values but using this function can return some extra stats as well.

Using LINEST()

This is an array function which means that is always returns more than one value.

This function uses the "least squares" method to calculate a best fit line.

All straight lines satisfy the equation "y=mx+b" and the "const" argument indicates if you want b=0.

When "const" is left blank TRUE is used and the function returns the same values as SLOPE and INTERCEPT.

This should be arranged in two columns. One column for the x-values and one for the y-values.

When "const" is FALSE the Intercept is assumed to be zero.

This function now only returns a single value and does not have to be entered as an array formula.

Using INTERCEPT() and SLOPE()

Every linear equation can be written in the following form "y=mx+c"

where y is the dependent y-value

m is the slope coefficient that corresponds to each x-value

c is the intercept (or constant) that represents the point at which the line crosses the y-axis

Additional Regression Statistics

This function can also be used to give you an indication as to how good this best fit line actually is.

You can also return some regression statistics

The "stats" argument indicates if you want the regression statistics listed. These are basically the error estimates.

This can now be used to evaluate the model you have built

The r2 (r squared) value is often used for this purpose and gives a "rough" indicator of a good fit. Values close to 1 are good.

The uncertainties in the slope and intercept are a much better indicator for a "good fit"

In this case the uncertainties in the slope and the intercept are not as good as the "r2" might have indicated

The F-Statistic

This is the ratio of the variance in the data explained by the linear model divided by the variance unexplained by the linear model.

An even better test for a "good fit" is to use the Fisher F-statistic.

Determining new Values

Once you know the values of m and b, you can calculate any point on the line by plugging the y- or x-value into that equation. You can also use the TREND function.

In order to determine new x-values for a given set of data points you need to solve the following linear equation

You can use the LINEST and INDEX functions to solve this equation.

The INDEX function allows you to retrieve any x-value from a given y-value

Residuals

The residual value is the difference between the value predicted by the equation and the value observed or collected.

If the equation is a close fit then we would expect the residuals to be randomly scattered around zero.

If they display a pattern then it is likely that a better equation exists.

Things to Remember

Plotting a graph and then adding a trendline gives you slightly more control as you can provide the value for the intercept.

The Linest function can be used with more than one set of values.

You can quickly enter the squared symbol by using (Alt + 0178).

You can quickly enter the cubed symbol by using (Alt + 0179).