How to use LINEST function in Excel - formula examples

LINEST function syntax

The syntax of the Excel LINEST function is as follows:

Where:

• known_y's (required) is a range of the dependent y-values in the regression equation. Usually, it is a single column or a single row.
• known_x's (optional) is a range of the independent x-values. If omitted, it is assumed to be the array {1,2,3,...} of the same size as known_y's.
• const (optional) - a logical value that determines how the intercept (constant a) should be treated:
  • If TRUE or omitted, the constant a is calculated normally.
  • If FALSE, the constant a is forced to 0 and the slope (b coefficient) is calculated to fit y=bx.
• stats (optional) is a logical value that determines whether to output additional statistics or not:
  • If TRUE, the LINEST function returns an array with additional regression statistics.
  • If FALSE or omitted, LINEST only returns the intercept constant and slope coefficient(s).

Additional statistics returned by LINEST

The stats argument set to TRUE instructs the LINEST function to return the following statistics for your regression analysis:

The below map shows the order in which LINEST returns an array of statistics:

In the last three rows, the #N/A errors will appear in the third and subsequent columns that are not filled with data. It is the default behavior of the LINEST function, but if you'd like to hide the error notations, wrap your LINEST formula into IFERROR as shown in this example.

Simple linear regression: calculate slope and intercept

To get the intercept and the slope of a regression line, you use the LINEST function in its simplest form: supply a range of the dependent values for the known_y's argument and a range of the independent values for the known_x's argument. The last two arguments can be set to TRUE or omitted.

For example, with y values (sales numbers) in C2:C13 and x values (advertising cost) in B2:B13, our linear regression formula is as simple as:

=LINEST(C2:C13,B2:B13)

To enter it correctly in your worksheet, select two adjacent cells in the same row, E2:F2 in this example, type the formula, and press Ctrl + Shift + Enter to complete it.

The formula will return the slope coefficient in the first cell (E2) and the intercept constant in the second cell (F2):

The slope is approximately 0.52 (rounded to two decimal places). It means that when x increases by 1, y increases by 0.52.

The Y-intercept is negative -4.99. It is the expected value of y when x=0. If plotted on a graph, it is the value at which the regression line crosses the y-axis.

Supply the above values to a simple linear regression equation, and you will get the following formula to predict the sales number based on the advertising cost:

y = 0.52*x - 4.99

For example, if you spend $50 on advertising, you are expected to sell 21 umbrellas:

0.52*50 - 4.99 = 21.01

The slope and intercept values can also be obtained separately by using the corresponding function or by nesting the LINEST formula into INDEX:

Slope

=SLOPE(C2:C13,B2:B13)

=INDEX(LINEST(C2:C13,B2:B13),1)

Intercept

=INTERCEPT(C2:C13,B2:B13)

=INDEX(LINEST(C2:C13,B2:B13),2)

As shown in the screenshot below, all three formulas yield the same results:

Multiple linear regression: slope and intercept

In case you have two or more independent variables, be sure to input them in adjacent columns, and supply that whole range to the known_x's argument.

For example, with sales numbers (y values) in D2:D13, advertising cost (one set of x values) in B2:B13 and average monthly rainfall (another set of x values) in C2:C13, you use this formula:

=LINEST(D2:D13,B2:C13)

As formula is going to return an array of 3 values (2 slope coefficients and the intercept constant), we select three contiguous cells in the same row, enter the formula and press the Ctrl + Shift + Enter shortcut.

Please note that the multiple regression formula returns the slope coefficients in the reverse order of the independent variables (from right to left), that is b_n, b_n-1, …, b₂, b₁:

To predict the sales number, we supply the values returned by the LINEST formula to the multiple regression equation:

y = 0.3*x₂ + 0.19*x₁- 10.74

For example, with $50 spent on advertising and an average monthly rainfall of 100 mm, you are expected to sell approximately 23 umbrellas:

0.3*50 + 0.19*100 - 10.74 = 23.26

Simple linear regression: predict dependent variable

Apart from calculating the a and b values for the regression equation, the Excel LINEST function can also estimate the dependent variable (y) based on the known independent variable (x). For this, you use LINEST in combination with the SUM or SUMPRODUCT function.

For example, here's how you can calculate the number of umbrella sales for the next month, say October, based on sales in the previous months and October's advertising budget of $50:

=SUM(LINEST(C2:C10, B2:B10)*{50,1})

Instead of hardcoding the x value in the formula, you can provide it as a cell reference. In this case, you need to input the 1 constant in some cell too because you cannot mix references and values in an array constant.

With the x value in E2 and the constant 1 in F2, either of the below formulas will work a treat:

Regular formula (entered by pressing Enter):

=SUMPRODUCT(LINEST(C2:C10, B2:B10)*(E2:F2))

Array formula (entered by pressing Ctrl + Shift + Enter):

=SUM(LINEST(C2:C10, B2:B10)*(E2:F2))

To verify the result, you can get the intercept and slope for the same data, and then use the linear regression formula to calculate y:

=E2*G2+F2

Where E2 is the slope, G2 is the x value, and F2 is the intercept:

Multiple regression: predict dependent variable

In case you are dealing with several predictors, i.e. a few different sets of x values, include all those predictors in the array constant. For example, with the advertising budget of $50 (x₂) and an average monthly rainfall of 100 mm (x₁), the formula goes as follows:

=SUM(LINEST(D2:D10, B2:C10)*{50,100,1})

Where D2:D10 are the known y values and B2:C10 are two sets of x values:

Please pay attention to the order of the x values in the array constant. As pointed out earlier, when the Excel LINEST function is used to do multiple regression, it returns the slope coefficients from right to left. In our example, the Advertising coefficient is returned first, and then the Rainfall coefficient. To calculate the predicted sales number correctly, you need to multiply the coefficients by the corresponding x values, so you put the elements of the array constant in this order: {50,100,1}. The last element is 1, because the last value returned by LINEST is the intercept that should not be changed, so you simply multiply it by 1.

Instead of using an array constant, you can input all the x variables in some cells, and reference those cells in your formula like we did in the previous example.

Regular formula:

=SUMPRODUCT(LINEST(D2:D10, B2:C10)*(F2:H2))

Array formula:

=SUM(LINEST(D2:D10, B2:C10)*(F2:H2))

Where F2 and G2 are the x values and H2 is 1:

LINEST formula: additional regression statistics

As you may remember, to get more statistics for your regression analysis, you put TRUE in the last argument of the LINEST function. Applied to our sample data, the formula takes the following shape:

=LINEST(D2:D13, B2:C13, TRUE, TRUE)

As we have 2 independent variables in columns B and C, we select a rage consisting of 3 rows (two x values + intercept) and 5 columns, enter the above formula, press Ctrl + Shift + Enter, and get this result:

To get rid of the #N/A errors, you can nest LINEST into IFERROR like this:

=IFERROR(LINEST(D2:D13, B2:C13, TRUE, TRUE), "")

The screenshot below demonstrates the result and explains what each number means:

The slope coefficients and the Y-intercept were explained in the previous examples, so let's have a quick look at the other statistics.

Coefficient of determination (R²). The value of R² is the result of dividing the regression sum of squares by the total sum of squares. It tells you how many y values are explained by x variables. It can be any number from 0 to 1, that is 0% to 100%. In this example, R² is approximately 0.97, meaning that 97% of our dependent variables (umbrella sales) are explained by the independent variables (advertising + average monthly rainfall), which is an excellent fit!

Standard errors. Generally, these values show the precision of the regression analysis. The smaller the numbers, the more certain you can be about your regression model.

F statistic. You use the F statistic to support or reject the null hypothesis. It is recommended to use the F statistic in combination with the P value when deciding if the overall results are significant.

Degrees of freedom (df). The LINEST function in Excel returns the residual degrees of freedom, which is the total df minus the regression df. You can use the degrees of freedom to get F-critical values in a statistical table, and then compare the F-critical values to the F statistic to determine a confidence level for your model.

Regression sum of squares (aka the explained sum of squares, or model sum of squares). It is the sum of the squared differences between the predicted y-values and the mean of y, calculated with this formula: =∑(ŷ - ȳ)². It indicates how much of the variation in the dependent variable your regression model explains.

Residual sum of squares. It is the sum of the squared differences between the actual y-values and the predicted y-values. It indicates how much of the variation in the dependent variable your model does not explain. The smaller the residual sum of squares compared with the total sum of squares, the better your regression model fits your data.