Leading the way in Microsoft Office Development

Excel

What is a Linear Regression ?

A linear regression produces the slope of a line that best fits a single set of data points.

For example a linear regression could be used to help project the sales for next year based on the sales from this year.

This assumes that growth will remain linear for the next year.

Excel includes several array functions for performing linear regressions:

LINEST - The array of values for a straight line that best fits the data.

TREND - The y-values along a linear trend given a set of x-values.

FORECAST - The future value along a linear trend by using existing values.

SLOPE - The slope of a linear regression line through the given data points.

STEYX - The standard error of the predicted y-values for each x regression.

What is an Exponential Regression ?

An exponential regression produces an exponential curve that best fits a single set of data points.

For example an exponential regression could be used to represent the growth of a population. This would be a better representation than using a linear regression.

Excel includes several array functions for performing exponential regressions:

LOGEST - The array of values for an exponential curve that best fits the data.

GROWTH - The predicted exponential growth using existing values.

What is a Multiple Regression ?

This is the analysis of more than one set of data points and can often produce more accurate results.

You can perform both linear and exponential regression analysis with more than one set of data points.

For example a multiple regression could be used to project the price of houses in your area based on their size, age and location.

Using Linear Regression

For more details please refer to the Regression page.

The equation "y = mx + b" describes a straight line for a single set of data points with one independent variable (x).

In this equation "y" is the dependent variable, "m" is the gradient of the slope and "b" is the point of interception with the y-axis.

In the case of multiple regression the equation becomes "y = m1x1 + m2x2 + ….. + mnxn + b".

In this equation "y" is the dependent variable, "x1" to "xn" are the independent variables and "mn" are the corresponding coefficients of each of the independent variables and "b" is a constant.

The LINEST() function uses this more general equation to return the values of "m1" through to "mn" and the given value of the constant "b".

The parameters that need to be passed to the function are the known set of values for "y" and a known set of values for each independent variable "x"

Things to Remember

Regression is often used to help predict the future.

The only difference between the LINEST() and LOGEST() functions is that the LINEST() function projects a straight line and LOGEST() projects an exponential curve.