TREND (values of a linear regression)

<< Click to Display Table of Contents >>

TREND (values of a linear regression)

Syntax:

TREND(y_values [, x_values] [, New_x_values] [, Constant])

Description:

Returns the values of a linear regression.

Linear regression is a statistical technique that adapts a line (called "trendline" or "best-fit line") to a set of data points (for example, the results of a series of measurements).

The TREND function returns an array with the y values of a trendline calculated from the given y values and x values.

In detail, the function expects the following arguments:

y_values: Here you specify the existing y-coordinates, that is, the dependent values. y_values can be either a cell reference or an array.

x_values: Here you can optionally specify the x-coordinates, that is, the independent values. Also x_values can be either a cell reference or an array. If x_values are omitted, the values 1, 2, 3, ... will be used automatically. (according to the number of existing y_values).

New_x_values (optional) are the x values for which you want to determine the corresponding y values on the curve. If omitted, the values in x_values will be used. However, if required, you can specify other x-coordinates with New_x_values. New_x_values can also be either a cell reference or an array.

Constant (optional) is a logical value that lets you specify if the constant b (the y-intercept point) should be calculated automatically or forced to equal zero:

TRUE or omitted: b will be calculated from the given data.

FALSE: b is forced to equal 0 (zero). This forces the trendline to run through the zero point. The m coefficient(s) are adjusted accordingly.

Note:

Formulas using this function have to be entered as an array formula. For more information, see Working with arrays.

Annotation:

The linear regression is performed with this function using the least squares method.

Example:

The resistance of a temperature-sensitive resistor has been measured at several temperatures.

Cells A1:A4 contain the temperatures measured: 8, 20, 25, 28

Cells B1:B4 contain the resistances measured: 261, 508, 608, 680

The following calculation returns an array with the y coordinates of a best-fit line calculated from these values:

TREND(B1:B4, A1:A4)

See also:

LINEST, LOGEST, GROWTH, FORECAST