How to Use LINEST Function in Excel (4 Suitable Examples)

The LINEST function is a built-in function in Excel that is categorized as a Statistical Function. It is one of those essential functions. In this tutorial, you will learn every detail of the LINEST function in Excel. This tutorial will be on point with suitable examples and proper illustrations.

The Excel LINEST function returns statistics for a reasonably fit linear line through supplied x and y values. The values produced by LINEST contain slope, intercept, standard error values, and more. To discover the best fit of a line to the data, LINEST uses the “least squares” procedure. The equation for the line is:

y = mx + b (for single range of x)

y = m1x1 + m2x2 + … + b (multiple ranges of x)

It returns an array of values. So you have to enter this as an array formula.

how to use the LINEST function in Excel

The above screenshot is an overview of the article. It represents a simplified application of the LINEST function in Excel. You’ll learn more about the dataset as well as the methods and procedures under different criteria in the following sections of this article.


Download Practice Workbook

Download this practice workbook.


Introduction to LINEST Function

Function Objective

Find the least-squares method to compute the statistics for a straight line and returns an array describing that line.

Syntax 

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

Arguments Explanations

Argument Required/Optional Explanation
known_y’s Required The set of y-values that we already know in the relationship y = mx + b.
  • If the range of known_y’s is in a single column, each column of known_x’s is decoded as a separate variable.
  • If the range of known_y’s is contained in a single row, each row of known_x’s is interpreted as a separate variable.
[known_x’s] Optional A set of x-values that we may already know in the relationship y = mx + b.
  • The range of known_x’s can include one or more sets of variables. If only one variable is used, known_y’s and known_x’s can be ranges of any shape, as long as they have equal dimensions.
  • For more than one variable is used, known_y’s must be a vector (that is, a range with a height of one row or width of one column).
  • If known_x’s is omitted, it is assumed to be the array {1,2,3,…} that is the same size as known_y’s.
[const] Optional A logical value specifying whether to force the constant b to equal 0.
  • If const is TRUE or omitted, b is calculated normally
  • If const is FALSE, b is set equal to 0 and the m-values are adjusted to fit y = mx.
[stats] Optional A logical value defining whether to return additional regression statistics.
  • If stats is TRUE, LINEST returns the additional regression statistics; as a result, the returned array is {mn,mn-1,…,m1,b;sen,sen-1,…,se1,seb;r2,sey;F,df;ssreg,ssresid}.
  • If stats is FALSE or omitted, LINEST returns only the m-coefficients and the constant b.

The following table will describe additional regression statistics:

Statistics Description
se1,se2,…,sen The standard error values for the coefficients m1,m2,…,mn.
seb The standard error value for the constant b (seb = #N/A when const is FALSE).
r2 The coefficient of determination. Compares estimated and actual y-values, and ranges in value from 0 to 1. If it is 1, there is a perfect correlation in the sample — there is no difference between the estimated y-value and the actual y-value. At the other extreme, if the coefficient of determination is 0, the regression equation is not helpful in predicting a y-value.
sey The standard error for the y estimate.
F The F statistic, or the F-observed value. Use the F statistic to determine whether the observed relationship between the dependent and independent variables occurs by chance.
df  The degrees of freedom. Use the degrees of freedom to help you find F-critical values in a statistical table. Compare the values you find in the table to the F statistic returned by LINEST to determine a confidence level for the model.
ssreg The regression sum of squares
ssresid The residual sum of squares. For information about how ssreg and ssresid are calculated, see “Remarks,” later in this topic.

Returns

The LINEST function returns an array of numeric values.

Available in

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000.


How to Use LINEST Function in Excel

As this is an array formula, sometimes pressing Enter won’t work. For that, you have to press Ctrl+Shift+Enter. After that, it will return an array of numeric values. After pressing that, our LINEST formula will have brackets ({}).

Look at the following screenshot. It will give you a clear idea.

how to use the LINEST function in Excel

As you can see, we have entered the LINEST function in Cell E5. As this is an array formula, our function has brackets. Here, we got two values including Slope(m) and Intercept(b).


4 Practical-life Examples of LINEST Function

In the following sections, we will provide you with four suitable and useful examples that will clear your idea about this function. We recommend you learn and practice all these examples to enrich your Excel knowledge.


1. Simple Linear Regression Using the LINEST Function

In statistics, linear regression is a linear approach for modeling the relationship between a scalar reaction and one or more descriptive variables (also known as dependent and independent variables).

A linear regression line has an equation of the form Y = b + mX, where X is the explanatory variable and Y is the dependent variable. The slope of the line is m, and b is the intercept (the value of y when x = 0).

Take a look at the following screenshot:

Here, we have a dataset of Age and Salary. Our goal is to find the slope and intercept.

📌 Steps

  • First, select the range of cells E5:F5.

select range of cells

  • Then, type the following formula:
=LINEST(C5:C10,B5:B10,TRUE,FALSE)

Here, Age = x and Salary = y

  • After that, press Ctrl+Shift+Enter.

Simple Linear Regression Using the LINEST Function

Here, you can see we have a slope of 131.7337. It means that when Age increases by 1, salary increases by $131.7337.

So, if the Age is 24, the salary will be:

Salary = $131.7337*24+(-574.7678)

= $2586.842

So, we have successfully used the LINEST function of Excel here.


2. Multiple Linear Regression in Excel

In Statistics, Multiple linear regression is used to estimate the relationship between two or more independent variables and one dependent variable.

Take a look at the following screenshot:

Here, our equation will be y = m2x2 + m1x1+ b

Where,

y= Salary

m2 = Slope of Experience

m1 = Slope of Age

x2 = Experience

x1= Age

Now. follow these simple steps to find the slopes and intercepts.

📌 Steps

  • First, select the range of cells F5:H5.
  • Then, type the following formula:
=LINEST(D5:D10,B5:C10,TRUE,FALSE)
  • After that, press Ctrl+Shift+Enter.

Linear Regression Using the LINEST Function

As you can see, we have successfully found all the values using the LINEST function in Excel.


Similar Readings


3. LINEST Function to Predict Dependent Variable for Simple Linear Regression in Excel

Apart from computing the slope and intercept for the regression equation, we can use the LINEST function to predict the dependent variable (y) based on the known independent variable (x). To perform this, we can use the LINEST function with the SUM function or the SUMPRODUCT function.

Take a look at the screenshot:

Suppose, you want to know the Salary of a person who is 37 years of Age based on previous data. Follow, the simple steps to accomplish this.

📌 Steps

  • First, type any of the following formula in Cell G5:

=SUM(LINEST(C5:C10,B5:B10,TRUE,FALSE)*(E5:F5))

Or,

=SUMPRODUCT(LINEST(C5:C10,B5:B10,TRUE,FALSE)*(E5:F5))

Or,

=SUMPRODUCT(LINEST(C5:C10,B5:B10,TRUE,FALSE)*{37,1})

  • Then, press Enter or Ctrl+Shift+Enter.

LINEST Function to Predict Dependent Variable for Simple Linear Regression in Excel

As you can see, we have successfully found the dependent variable Salary based on Age using the LINEST function.

To verify this, we are using the y=mx + b formula. Find the Slope and intercept as we showed earlier. Then, type the following formula:

=E8*37+F8

LINEST Function to Predict Dependent Variable for Simple Linear Regression in Excel

As you can see, the result is the same as before.


4. LINEST Function to Predict Dependent Variable for Multiple Linear Regression in Excel

If you have multiple independent variables (x), you can also find the dependent variable based on this. Here, we are also going to use the LINEST function with the SUM function or the SUMPRODUCT function.

Here, our goal is to find the Salary of a person who is 28 years of age and has 5 years of experience.

📌 Steps

  • First, type any of the following formula in Cell I5:

Regular Formula:

=SUMPRODUCT(LINEST(D5:D10,B5:C10,TRUE,FALSE)*(F5:H5))

Or,

Array Formula:

=SUM(LINEST(D5:D10,B5:C10,TRUE,FALSE)*(F5:H5))

Or,

=SUM(LINEST(D5:D10,B5:C10,TRUE,FALSE)*{5,28,1})

  • Then, press Enter or Ctrl+Shift+Enter.

LINEST Function to Predict Dependent Variable for Multiple Linear Regression in Excel

As you can see, we have successfully used the LINEST function to find the Salary based on Experience and Age.

To verify this, we are using the formula : y = m2x2 + m1x1 + b

Find the Slope and intercept as we showed earlier. Then, type the following formula in Cell G10:

=F8*5+G8*28+H8

LINEST Function to Predict Dependent Variable for Multiple Linear Regression in Excel

Here, you can see our LINEST function gave the exact result like using the Linear Regression formula.


Additional Regression Statistics by LINEST Function in Excel

If you have read the previous sections, you have noticed that we set the [stats] to FALSE. We did it because we didn’t want additional statistics. If you want the regression analysis, then set it to TRUE.

For simple linear regression, use the following formula:

=LINEST(C5:C10,B5:B10,TRUE,TRUE)

Additional Regression Statistics by LINEST Function in Excel

For multiple linear regression, type the following formula:

=LINEST(D5:D10,B5:C10,TRUE,TRUE)

Additional Regression Statistics by LINEST Function in Excel

To remove the #N/A error, type the following formula:

=IFERROR(LINEST(D5:D10,B5:C10,TRUE,TRUE),"")

Now, look at the following screenshot to know the meaning:

Additional Regression Statistics by LINEST Function in Excel


💬 Things to Remember

If you omit the [const] argument, it will consider it TRUE. And our equation will be y=mx+b. But, if you set it FALSE it will consider the constant 0. And equation will be y=mx.

If the known_x’s and known_y’s ranges have different dimensions, it will show #REF! Error

If known_x’s or known_y‘s contains at least one blank cell, text value, or a text representation of a number, it will show #VALUE! Error

Also, if the const or stats argument cannot be evaluated to TRUE or FALSE, it will show the #VALUE error.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to use the LINEST function in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo