LINEST function - The function returns a line description array using the least square method in Excel

The following article introduces you to the LINEST function - one of the functions in the statistical function group is very popular in Excel.

LINEST function - The function returns a line description array using the least square method in Excel Picture 1

Description: The function returns the line description array using the least squares method.

Syntax: LINEST (known_y's, [known_x's], [const], [stats])

Inside:

- known_y's : The set of known y values ​​in the relationship y = b * m ^ x, is a required parameter.

+ If known_y's is in a single column or row -> each known_y's column or row is interpreted as a separate variable.

- known_x's: The set of known x values ​​in the relationship y = b * m ^ x, is an optional parameter.

+ known_x's may include 1 or more sets of variables.

+ If known_x's is omitted -> it is assumed to be an array of the same size as known_y's

- const: The logical value determining the value of the constant b, is an optional value including the following values:

+ const = True or ignore -> b is calculated normally.

+ const = False or ignore -> b = 1 and m are adjusted such that: y = m ^ x.

- stats: Logical values ​​that determine the type of additional regression statistics returned include: {mn, mn-1, ., m1, b; sen, sen-1, ., se1, seb ; r2, sey; F, df; ssreg, ssresid}.

Attention:

- Can describe any straight line by slope and intersection y:

+ Slope (m):

To find the slope of line m, take two points on that line, (x1, y1) and (x2, y2) -> slope equal to (y2 - y1) / (x2 - x1).

+ Intersection Y (b):

The intersection y of a line b, is the value of y at the point where the line intersects the y-axis.

The equation of the line is y = mx + b . You can calculate any point on a straight line by entering the value y or x into the equation.

- When there is only one independent variable -> use the formula:

+ Slope: 

= INDEX (LINEST (known_y's, known_x's), 1)

+ Crossing Y:

= INDEX (LINEST (known_y's, known_x's), 2)

+ Use commas to separate values ​​in the same row.

+ Use semicolons separating between rows together.

- When there are some independent variables x -> use the formula:

[m = frac {{sum {left ({x - overline x} right) left ({y - overline y} right)}}} {{sum {{{left ({x - overline x} right)} ^ 2 }}}}]

[b = overline y - moverline x]

Where:   x = AVERAGE (known x's ) and  y = AVERAGE (known_y's).

For example:

Calculate the slope and y intersection of the line corresponding to the values ​​of x and y in the data table below:

LINEST function - The function returns a line description array using the least square method in Excel Picture 2

- Calculate the slope m of the line corresponding to the values ​​of x and y. In a cell to calculate enter the formula : = LINEST (C6: C10, D6: D10, FALSE)

LINEST function - The function returns a line description array using the least square method in Excel Picture 3

- Press Enter -> slope m of the line corresponding to the values ​​of x and y are:

LINEST function - The function returns a line description array using the least square method in Excel Picture 4

- Calculate the intersection y of the line corresponding to the values ​​of x and y. In the cell to calculate enter the formula : = LINEST (C6: C10, D6: D 10, TRUE)

LINEST function - The function returns a line description array using the least square method in Excel Picture 5

- Press Enter -> y intersection of the line corresponding to the values ​​x, y are:

LINEST function - The function returns a line description array using the least square method in Excel Picture 6

Above are instructions and some specific examples when using the LINEST function in Excel.

Good luck!