# Documentation/Calc Functions/LINEST

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

## Function name:

LINEST

## Category:

Array

## Summary:

Returns a table of statistics for a straight line that best fits a data set.

## Syntax:

LINEST(**data_Y**; **data_X**; **linearType**; **stats**)

## Returns:

## Arguments:

**data_Y** is a single row or column range specifying the y coordinates in a set of data points.

**data_X** is a corresponding single row or column range specifying the x coordinates. If **data_X** is omitted it defaults to 1, 2, 3, ..., n. If there is more than one set of variables **data_X** may be a range with corresponding multiple rows or columns.

LINEST finds a straight line y = a + bx that best fits the data, using linear regression (the "least squares" method). With more than one set of variables the straight line is of the form y = a + b1x1 + b2x2 ... + bnxn.

If **linearType** is FALSE the straight line found is forced to pass through the origin (the constant a is zero; y = bx). If omitted, **linearType** defaults to TRUE (the line is not forced through the origin).

If **stats** is omitted or FALSE only the top line of the statistics table is returned. If TRUE the entire table is returned.

LINEST returns a table (array) of statistics as below and must be entered as an array formula (for example by using *Ctrl + Shift + Return* rather than just *Return*).

In the LibreOffice Calc functions, parameters marked as "optional" can be left out only when no parameter follows. For example, in a function with four parameters, where the last two parameters are marked as "optional", you can leave out parameter 4 or parameters 3 and 4, but you cannot leave out parameter 3 alone.

More explanations on top of this page.

## Additional details:

## Examples:

This function returns an array and is handled in the same way as the other array functions. Select a range for the answers and then the function. Select **data_Y**. If you want, you can enter other parameters. Select **Array** and click **OK**.

The results returned by the system (if **stats** = 0), will at least show the slope of the regression line and its intersection with the Y axis. If **stats** does not equal 0, other results are to be displayed.

### Other LINEST Results

Examine the following examples:

A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|

1 | x1 | x2 | y | LINEST value | |||

2 | 4 | 7 | 100 | 4,17 | -3,48 | 82,33 | |

3 | 5 | 9 | 105 | 5,46 | 10,96 | 9,35 | |

4 | 6 | 11 | 104 | 0,87 | 5,06 | #N/A | |

5 | 7 | 12 | 108 | 13,21 | 4 | #N/A | |

6 | 8 | 15 | 111 | 675,45 | 102,26 | #N/A | |

7 | 9 | 17 | 120 | ||||

8 | 10 | 19 | 133 |

Column A contains several X1 values, column B several X2 values and column C the Y values. You have already entered these values in your spreadsheet. You have now set up E2:G6 in the spreadsheet and activated the *Function Wizard*. For the LINEST function to work, you must have marked the **Array** check box in the *Function Wizard*. Next, select the following values in the spreadsheet (or enter them using the keyboard):

**data_Y** is C2:C8

**data_X** is A2:B8

**linearType** and **stats** are both set to 1.

As soon as you click **OK**, LibreOffice Calc will fill the above example with the LINEST values as shown in the example.

The formula in the *Formula bar* corresponds to each cell of the LINEST array {=LINEST(C2:C8;A2:B8;1;1)}.

### This represents the calculated LINEST values

E2 and F2: Slope m of the regression line y=b+m*x for the x1 and x2 values. The values are given in reverse order; that is, the slope for x2 in E2 and the slope for x1 in F2.

G2: Intersection b with the y axis.

E3 and F3: The standard error of the slope value.

G3: The standard error of the intercept

E4: RSQ

F4: The standard error of the regression calculated for the Y value.

E5: The F value from the variance analysis.

F5: The degrees of freedom from the variance analysis.

E6: The sum of the squared deviation of the estimated Y values from their linear mean.

F6: The sum of the squared deviation of the estimated Y value from the given Y values.

More explanations on top of this page.