Documentation/Calc Functions/LINEST

From The Document Foundation Wiki
Jump to: navigation, search


Im-jabber.svg

Under construction.
For now, see the system Help files.


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.

Related LibreOffice functions:

ODF standard:

Equivalent Excel functions: