Linest Function In Excel How To Use Linest In Excel Examples

LINEST Function in Excel

It is a built-in function in MS Excel. The LINEST function in Excel is used to calculate the statistics for a line. The LINEST function in Excel uses the least-squares regressionThe Least-squares RegressionVBA square root is an excel math/trig function that returns the entered number’s square root. The terminology used for this square root function is SQRT. For instance, the user can determine the square root of 70 as 8.366602 using this VBA function.read more and returns an array that describes the straight line best fits given data.

LINEST Formula in Excel

Below is the LINEST Formula in Excel.

The =Equation for the Line is:

y = mx + b

–or–

y = m1x1 + m2x2 + m3x3 + … + b

The LINEST in Excel has two arguments, out of which one is required. When

known_y’s = It is a required parameter and indicates the set of y-values already known 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 interpreted as a separate variable.Likewise, if the range of known_y’s is in a single row, each row of known_x’s is interpreted as a different variable.known_x’s = It is an optional parameter and indicates the set of x-values already known in the relationship y = mx + b.The range of known_x’s can have one or more sets of variables. If only one variable is used, known_y’s and known_x’s can be the ranges of any shape with equal dimensions.If more than one variable is used, known_y’s must be a vector (i.e., a range with a height of one row or width of one column).const = It is an optional parameter and represents a logical value (TRUE/FALSE) specifying whether to force the constant b to be equal to 0. If const is TRUE or skipped, 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 = It is an optional parameter and represents a logical value (TRUE/FALSE) specifying whether to return additional regression statistics. If stats is TRUE, LINEST in excel 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 are FALSE or skipped, LINEST in Excel returns only the m-coefficients and the constant b.

How to Use the LINEST Function in Excel?

The said function is a Worksheet (WS) function. As a WS function, the LINEST function in Excel can be entered as a part of the formula in a worksheet cell. You can refer to a couple of examples given below to know more.

If the range of known_y’s is in a single column, each column of known_x’s is interpreted as a separate variable.Likewise, if the range of known_y’s is in a single row, each row of known_x’s is interpreted as a different variable.
The range of known_x’s can have one or more sets of variables. If only one variable is used, known_y’s and known_x’s can be the ranges of any shape with equal dimensions.If more than one variable is used, known_y’s must be a vector (i.e., a range with a height of one row or width of one column).
If const is TRUE or skipped, b is calculated normally. If const is FALSE, b is set equal to 0, and the m-values are adjusted to fit y = mx.
If stats is TRUE, LINEST in excel 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 are FALSE or skipped, LINEST in Excel returns only the m-coefficients and the constant b.

Let us look at the examples given below. Each instance covers a different use case implemented using the LINEST function in Excel.

LINEST in Excel Example #1 – Slope

=LINEST (B2:B5, C2:C5, , FALSE)

The above formula shows that B2:B5 is the known_y’s, and C2:C5 is known_x’s. The third parameter, i.e., const, is left blank so that it will be calculated. Finally, the fourth parameter, stats, is marked FALSE.

LINEST in Excel Example #2 – Simple Linear Regression

= SUM (LINEST(B1:B6, A1:A6)*{9,1})

As shown in the above LINEST formula in Excel, A1:A6 is the month number, and B2:B6 indicates the corresponding sales figures. So, based on the six-month sales data, sales data for the 9th month is to be estimated.

Things to Remember

When entering an array constant (known_x’s) as an argument, we can use commas to separate values in the same row and semicolons to separate the rows. Separator characters may vary depending on native regional settings.The y-values predicted by the regression equation may not be valid if they are outside the range of the y-values used to determine the equation.Formulas that return arrays must be entered as array formulas.When there’s only one independent x-variable, slope and y-intercept values can directly be calculated by using the following formulas:Slope: =INDEX (LINEST (known_y’s, known_x’s),1)Y-intercept: =INDEX (LINEST (known_y’s, known_x’s),2)We can describe a straight line with slope and y-intercept:Slope (m): To find the slope of a line, often represented as m: – For a line with two points (x1, y1) and (x2, y2); the slope is calculated as m = (y2 – y1) / (x2 – x1).Y-intercept (b): The y-intercept of a line, often represented as b, is the value of y at the point where the line crosses the y-axis.The equation of a straight line is y = mx + b. Once the values of m and b are known, we can calculate any point on the line by putting the y- or x-value into the equation. For example, see the TREND function in Excel.

Linest Function In Excel How To Use Linest In Excel Examples