Excel linear regression Fitting line trend function is used in this way

Functions of a brief

Function Name: Trend

function function: Returns the value of a linear regression fitting line.

That is, the line that fits the given group known_y ' s and known_x ' s is found (with the least squares) and returns the Y-value of the specified array new_x ' s on the line.

function syntax and parameter description:

TREND (known_y ' s, [known_x ' s], [new_x '], [const]) TREND function syntax has the following parameters parameter: A value that provides information for an action, event, method, property, function, or procedure. )：

Known_y ' s required. Relational expression y = The set of y values known in mx + B. If the array known_y ' s is in a separate column, each column of known_x ' is treated as a separate variable. If the array known_y ' s is on a separate line, each row of known_x ' s is treated as a separate variable.

Known_x ' s required. Relational expression y = The set of optional x values known in MX + B.

Array known_x ' s can contain one or more sets of variables. If only one variable is used, then as long as known_x ' s and known_y ' s have the same number of dimensions, they can be regions of any shape. If multiple variables are used, the known_y ' s must be a vector (that is, a row or column).

If known_x ' s is omitted, the array is assumed to be {1,2,3,...} with the same size as known_y ' s.

New_x ' s required. A function TREND is required to return the new X value of the corresponding Y-value. New_x ' s, like known_x ' s, must include a separate column (or row) for each argument. Therefore, if known_y ' s is a single column, known_x ' s and new_x ' s should have the same number of columns. If known_y ' s is a single row, known_x ' s and new_x ' s should have the same number of rows. If new_x ' s is omitted, it is assumed to be the same as known_x ' s. If known_x ' s and new_x ' s are omitted, they are assumed to be in the array {1,2,3,...}, the same size as known_y ' s.

Const Optional. A logical value that specifies whether to force constant B to be set to 0.

If the const is TRUE or omitted, B will be evaluated as normal.

If the const is FALSE,B will be set to 0 (0), M will be adjusted to make y = mx.

Two, Trend function example

For example, there are two columns of data, columns A and B, with the following data

Column A columns B

1 133890

2 135000

3 135790

4 137300

5 138130

6 139100

7 139900

8 141120

9 141890

10 143230

11 144000

12 145290

For the data above, to calculate the corresponding cost, we can use the following formula:

=trend (B2:B13,A2:A13)

If you want to calculate the forecast cost, we should use the following formula:

=trend (B2:B13,A2:A13,A15:A19)