How To Use LINEST Function In Excel

Share on facebook
Share on twitter
Share on pinterest

In statistical modeling, regression means studying the relationship between a variable (dependent variable) and one or more variables (independent variable). Among Excel statistical functions, the LINEST function is used for linear regression calculations. In this blog, we are going to introduce the LINEST function and discuss how it works.

Introduction to Linear Regression

As mentioned above, in linear regression, we have an independent variable and a dependent variable, so we seek to examine the relationship between the dependent and independent variables. The use of linear regression is to estimate the value of the dependent variable using the independent variable and the regression equation.

If we have one independent variable, the regression is called simple linear regression, but when we have more than one independent variable, it is called multiple linear regression.

The equation for the simple linear equation is:

y=mx+b

In this equation, m is called “slope” and b is called “intercept.”

The equation for multiple linear regression is:

y=m1x1+ m2x2+…+mnxn+b

LINEST Function

In Excel, the LINEST function is used to do linear regression. The LINEST function uses the least square method to do linear regression calculations. The syntax for the LINEST function in Excel is as follows:

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

known_y’s: The dependent values of the line equation (y values).

[known_x’s]: The independent values of the line equation (x values). This argument is optional. If omitted, Excel assumes the dataset is {1,2,3,…} with the same size as y values.

[const]: This is the function’s logical argument, which says how the intercept should be treated (optional). 1 is TRUE and means that b should be calculated normally. 0 is FALSE and means that b should be considered TRUE. If omitted, Excel will consider it as zero.

[stats]: This a logical argument too. 1 is TRUE and means that Excel will return the statistical parameters. 0 is FALSE and means that Excel will not calculate the statistical parameters.

Note: For the logical arguments, you can type TRUE or FALSE, too.

The LINEST function returns an array of the statistical parameters which, includes:

ParameterDescription
miThe slope related to the ith variable
bThe intercept of the line (the constant value of y when x=0)
seiThe standard error values for slope coefficients (mi) of independent variables (xi) ( It is also called the uncertainty of slope)
sebThe standard error value for constant b. (It is also called the uncertainty of intercept)Note: If the [const] variable is zero, returns #N/A error
r2The constant of determination
seyThe standard error for y estimate
FF statistic, or the F-observed value
dfDegrees of freedom
ssregThe regression sum of squares
ssresidThe residual sum of squares

Since the LINEST function returns an array of outputs, it must be entered as an array formula using Ctrl+Shift+Enter shortcut.

Now let’s see how the LINEST function works for simple and linear regression:

Simple Linear Regression

In this example, we are going to calculate the slope, the intercept, and the statistical values for a dataset, including x and y values. To do the calculation:

  1. Select a range consisting of 2 rows and 5 columns.
  2. Type the syntax of the function
  3. Press Ctrl+Shift+Enter

The result will appear.

Simple linear regression using the LINEST function in Excel

The order of data the LINEST function returns is as follows:

m (the coefficient of slope)b (the coefficient of intercept)
seseb
r2sey
Fdf
ssregssresid

Note1: If you do not need the statistical parameters, just put zero for the last argument. In this case, you must select a range of two cells in one row.

Note2: If you do not enter the function as an array or select only one cell and type the formula, the function will only return the slope coefficient (m).

If you need to calculate an unknown y value corresponding to an x value, you will have two choices. The first way is to put the slope and intercept that the LINEST function returns in the line equation. The second way is to combine the LINEST function with the SUM function.

To use the second way for this example, you should type the following formula in an Excel cell and then press Ctrl+Shift+Enter.

=SUM(LINEST(B2:B11,A2:A11)*{45,1})

Note: If you need to enter the x value that you want to know its corresponding y value from a cell, you must enter the constant 1 in a cell too. Look at the image below:

How to predict dependent variable in simple linear regression using the LINEST function in Excel

SLOPE and INTERCEPT Functions

If you demand to find only the slope or intercept of the data, there is an alternate method. The SLOPE function is used to calculate the slope of the linear regression equation, and the INTERCEPT function is used to calculate the intercept of the linear regression equation. The syntax of these functions is as bellows:

SLOPE(known_y’s,known_x’s)

INTERCEPT(known_y’s,known_x’s)

known_y’s: The dependent values of the line equation (y values).

known_x’s: The independent values of the line equation (x values).

In the example below, we have calculated the slope and intercept of the regression line in E5 and E6 cells, respectively. Then we calculated the y value corresponding to x=9.5.

How to use the slope and intercept functions in Excel

Note: The algorithm that the SLOPE and INTERCEPT functions use to calculate the slope and intercept are different from the algorithm used by the LINEST function, so that’s why you might see a difference in the slope value of a dataset using these functions.

In this example, we use the LINEST function to do multiple linear regression. The only difference between multiple and linear regression is the cells we select. If we have n datasets of x values, we should select (n+1) rows and 5 columns (in case we need the statistical parameters).

The order of parameters that the LINEST function returns is as follows:

mnmn-1m1b
sensen-1se1seb
r2sey
Fdf
ssregssresid

In the empty cells, Excel will return the #N/A error. It is the default behavior of the LINEST function.

For this example:

  1. Select a range consisting of 4 rows and 5 columns.
  2. Type the syntax of the function.
  3. Press Ctrl+Shift+Enter
Simple linear regression using the LINEST function in Excel

Attention: As you can see, the LINEST function returns the slopes and standard error of slope in reverse order.

Note: If you do not need the statistical parameters, just put zero for the last argument. In this case, you must select a range of 4 cells in one row.

If you demand to calculate an unknown y value corresponding to a set of x values, you can use the two methods mentioned in the previous example. If you want to use the combination of the LINEST and SUM functions, you have to type the x values in the reverse order. Look at the image below:

How to predict dependent variable in multiple linear regression using the LINEST function

Note: You can use the same method we introduced for the simple linear regression to enter the x values in cells.

How to predict dependent variable in multiple linear regression by entering x value in a cell using the LINEST function

Possible Errors

#REF!: This error will occur if the size of the first and second data sets is not equal.

#VALUE: This error occurs when each one of known_x’s and known_y’s has non-numeric values. It could be blank or text. It also occurs when the input for [const] or [stats] can not be evaluated to TRUE or FALSE.

Bottom Line

The LINEST function is a tool for calculating linear regression. So when you want to use the LINEST function, make sure that your data are close to linear. The uncertainty values and the other statistical parameters can help you determine the accuracy of the equation and whether or not the function fits the data.

You can connect with us,  contact our experts if you have any inquiries, and get more support via Excel Support Services.

Also, reduce costs, accelerate tasks, and improve quality with Excel Automation Services.

Subscribe to our Newsletter

Share this post with your friends

Share on facebook
Share on google
Share on twitter
Share on linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *