LINEST Function in Excel (Table of Contents)
LINEST in Excel
LINEST function is a built on function in excel which comes under the category of Statistical function which uses “LEAST SQUARES” method to find the line of ex
act fit through a set of array values i.e x and y values.
This LINEST function is a very useful function in excel to fit a line(y=mx+b) to the data in order to identify the relationship between two values i.e. x and y values.
LINEST Function uses the following line equation:
Y=mx+b ( for single range of x values)
Y=m1x1 +m2x2 +….+b( for multiple range of x values)
LINEST Formula in Excel
Below is the LINEST Formula in Excel :
LINEST Function in Excel includes the following parameters:
- known_y’s: The known y’s is n range or array of y values from the line equation.
- known_x’s: The known x’s is a range or array of x values from the line equation. If this x value is null excel will assume those x_values as 1,2,3..with the same number of values as y values.
- const: The const is a logical value that specifies either “TRUE” or “ FALSE”.
- stats: The stat is a logical value that specifies either to return additional regression statistics i.e “TRUE” or “FALSE” which function needs to return the statistics on the line of best fit.
Steps to Use the LINEST Function in Excel:
Click the formula tab in the menu. Select the Insert function. We will get the dialog box as shown below and choose the statistical option under this you will get a list of function where we can find the LINEST.
In Excel 2010 and the advanced version, we can see the more function tab where we can find the LINEST function under the category Statistical and the screenshot is shown below.
How to Use the LINEST Function in Excel?
LINEST Function in Excel is very simple and easy to use. Let understand the working of LINEST Function in excel by some LINEST Formula example.
Example #1 – Single Range of X values
To use the LINEST as array formula then you need to do the following steps :
- Select the cell where the function is and press f2.
- Press CTRL +SHIFT +ENTER.
In this LINEST Function in Excel example, we are going to see how LINEST function work with the data. Enter the data in excel with two data caption named as X and Y.
In order to use the LINEST function to find the exact result, Go to Formulas and choose the More function. Choose the LINEST Function under the statistical category as shown below.
Choose the LINEST function and you will get the below dialog box as shown below:
Once the dialogue box appears to choose the Known Y’s & Know X’s and mention the logical values “TRUE” depends on the data specified. i.e select B2:B11 as Known Y’s, A2:A11 as Known X’s and mention the logical condition as TRUE to get the specified value. After you click OK.
You will get the same value which is the coefficient m in the equation y=mx+b
So the Result will be :
As mentioned above we need to press CTRL+SHIFT+ENTER to get the exact data. Now we can see that formula is enclosed with two parentheses i.e. { } where LINEST function is evaluated.
We can mention a straight line with slope and y-intercept. In order to get the intercept and slope regression, we can use LINEST function lets see an example with step by step procedure.
Example #2
In this example, we are going to see how to use LINEST function in excel. This function is used to calculate the line of Coefficient.
Line Equation : Y=mx+c
Using LINEST Function in Excel we are going to calculate:
- A line of Best Fit gradient
- A line of best fit intercept
- The standard error of the gradient
- The standard error of the intercept
- R2
- Regression sum of squares
- Residual sum of squares.
Consider the below data which has X1 and Y1 values shown below:
To calculate the above equation select the cell and insert the LINEST function shown below.
Use CTRL+SHIFT+ENTER to get all values where we can see formula contains open and closing parenthesis.
Let’s see the same data how we can derive the same equation in a chart :
Choose X1 and Y2 Data and Go to insert option and select the chart type as shown below. And then click ok.
So that scatter chart graph will be displayed with the selected x and y data.
Now we are going to add a trend line to show exactly by selecting the scattered graph as shown below.
Right, click on the chart and Select “Add TrendLine”.
Once you selected the option “Add Trendline” a new trend line will be added in the chart as shown below.
Right-click again and choose Format Trendline and you will get the Trendline option.
Where it shows various statistical parameters like exponential, Liner, Logarithmic and polynomial.
Here choose the polynomial option with an order 2 as shown below screenshot.
Scroll down and check to mark the display equation on chart and display an R-Square value in the chart.
So the equation has been displayed in the chart as shown below with the same line equation.
Example #3 – LINEST Functioning for Multiple Range of X Values:
Consider the below example which has the same X1 and Y data and X2 values.
The following chart has been evaluated by using the scattered graph by adding a trend line function.
Assume the equation for Y=b+m1*X1+m2*X2
Lines Function : LINEST (Known_y’s,[Known_X’s],[const],[stats])
Consider the below array of a table which denotes as follows:
Where
- m1 – denotes X
- m2- denotes X2
- Const- denotes b
LINEST Function Used in Earlier & Latest Version.
In the earlier version LINEST function is used as a formula that is not correct to find the total sum of squares if the third argument to LINEST function is set to false and this causes an invalid value for regression sum of squares and also values are not correct for the other output sum of squares and the collinearity value caused round of error, standard errors of regression coefficient that are not given exact results, degrees of freedom that are not appropriate.
In excel 2003 LINEST function has been improved and given good result by adding the TREND function to make it appropriate.
Things to remember about the LINEST Function in Excel
- LINEST function in Excel should be used with appropriate values if not we will not get the exact result.
- LINEST function in Excel will not work If the array of Known_x’s is not as same as the array of Known_y’s.
Recommended Articles
This has been a guide to the LINEST in Excel. Here we discuss the LINEST Formula in Excel and How to use the LINEST Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
