EDUCBA

Home Excel Excel Resources Excel Functions LINEST Excel Function

LINEST Excel Function

By Karthikeyan SubburamanKarthikeyan Subburaman

LINEST Function in Excel

LINEST Function in Excel (Table of Contents)

LINEST in Excel

Linest function in excel is a statistical function used to calculate straight-line statistics and return an array from the available selected data, which also describes that line. In other words, Linest function calculates the statistics of a simple line equation (Y = mx + C) which also explains the relationship between the dependent and independent variables using the least square procedure to find the best solution for data used in.

LINEST Formula in Excel

Below is the LINEST Formula in Excel :

LINEST Formula in Excel

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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.

LINEST Function Step 1

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.

LINEST Function Step 2

How to Use the LINEST Function in Excel?

LINEST Function in Excel is very simple and easy to use. Let us understand the working of LINEST Function in excel by some LINEST Formula example.

You can download this LINEST?Function Excel Template here – LINEST?Function Excel Template

Example #1

To use the LINEST as an 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 the LINEST function works with the data. Enter the data in excel with two data caption named as X and Y.

LINEST Example 1-1

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.

LINEST function

Choose the LINEST function, and you will get the below dialog box as shown below:

LINEST Function Dialog Box 1

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.

LINEST Function Dialog Box 2

You will get the same value which is the coefficient m in the equation y=mx+b

So the result will be :

Result of Example 1

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 the 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 the LINEST function lets see an example with step by step procedure.

Example #2

In this example, we are going to see how to use the 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:

LINEST Example 2-1

To calculate the above equation, select the cell and insert the LINEST function shown below.

Calculation of LINEST function

Use CTRL+SHIFT+ENTER to get all values where we can see the formula contains open and closing parenthesis.

LINEST Example 2-3

Let’s see the same data how we can derive the same equation in a chart :

Choose X1 and Y2 Data and Go to the insert option and select the chart type as shown below. And then click ok.

Scatter Chart

So that scatter chart graph will be displayed with the selected x and y data.

LINEST Example 2-5

Now we are going to add a trend line to show exactly by selecting the scattered graph as shown below.

LINEST Example 2-6

Right, click on the chart and select “Add TrendLine”.

Add TrendLine

Once you selected the option “Add Trendline,” a new trend line will be added in the chart, as shown below.

LINEST Example 2-8

Right-click again and choose Format Trendline, and you will get the Trendline option.

Format trend line

Where it shows various statistical parameters like exponential, Liner, Logarithmic and polynomial.

Statistical parameters

Here choose the polynomial option with an order 2 as shown below screenshot.

Polynomial option

Scroll down and check to mark the display equation on the chart and display an R-Square value in the chart.

LINEST Example 2-12

So the equation has been displayed in the chart as shown below with the same line equation.

Result of Example 2

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.

LINEST Example 3-1

The following chart has been evaluated by using the scattered graph by adding a trend line function.

Result of Example 3

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:

LINEST Example 3.3

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 results by adding the TREND function to make it appropriate.

Things to Remember

  • The 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 a downloadable excel template. You can also go through our other suggested articles –

  1. Line Break in Excel
  2. New Line in Excel Cell
  3. Linear Regression in Excel
  4. Excel Gridlines
7 Shares
Share
Tweet
Share