TREND


Description Computes result values from the fitted curve of a multiple linear regression for a group of specified observations relative to a group of specified independent variables. This function is useful for extrapolating information based on a trend in existing data.

Note If you want TREND to return more than one result value, you must enter it as an array function. For information, see Array Functions.

Syntax TREND (known_y's [, known_x's] [, new_x's] [, constant])
Argument Description
known_y's A list of observed values for the dependent variable. This must be a range reference or an array constant containing all numeric values and no empty cells. For a regression with a single independent variable, enter any type of range. For a regression with multiple independent variables, enter a single row or column of values. If you are using array constants, see Array Constants in Arguments for information on how to enter the array.
[known_x's] Optional. A list of observed values for the independent variable(s). This must be a range reference or an array constant containing all numeric values and no empty cells. For a regression with a single independent variable, when known_y's is a single row or column, enter a range that exactly matches the size and shape of the known_y's range. When known_y's is more than one row and column, known_x's must be a range containing the same number of values as the known_y's range, although the two ranges may be different shapes. For a regression with multiple independent variables, when known_y's is a single row, enter a contiguous row of values for each independent variable, The number of columns must match the number of columns in known_y's. Similarly, when known_y's is a single column, enter a contiguous column of values for each independent variable, ensuring that the number of rows matches known_y's. If you are using array constants, see Array Constants in Arguments for information on how to enter the array. To fit the regression to a formula that uses polynomials, see "Polynomials," below. When you omit this argument, the function assumes a single independent variable of the sequence {1, 2, 3, ...}.
[new_x's] Optional. A list of values for the independent variable(s) that will compute the results of this function. This must be a range reference or an array constant containing all numeric values and no empty cells. You may enter any number of values in new_x's. The shape of the range only matters if this is a regression with multiple independent variables, in which case it must have the same number of rows or colunms of independent variables as known_x's. If you omit this argument, the function uses the specified or default values in known_x's.
[constant] Optional. A logical value that determines whether or not a constant is to be included. The constant allows the regression line to intercept the Y axis at a point other than (0,0). Use True (the default value if this argument is omitted) to include the constant. Use False to force the line to intercept the Y axis at 0.

Remarks This function uses LINEST internally to compute the regression's coefficients. The resulting formula is then used to compute the requested values.

Results range Before entering this function, select a results range according to the following criteria:

Equation The regression computed by TREND attempts to fit the following formula:

y = C1x1+C2x2+ ... + CnXn+b

where C is the coefficient determined internally by the LINEST function, n is the number of independent variables, and b is the constant.

Polynomials To create a polynomial regression, the data for the independent variable must be set up carefully. Create a worksheet in which column A represents x and contains the appropriate data or formulas. For column B, which will contain x2, fill the cells with a formula such as =A1^COLUMN(), which will raise the data in column A to the 2nd power. Fill column C with the same formula to raise the data in column A to the 3rd power.

The data for the new_x's variable should be set up in the same manner.

Example The following example of multiple linear regression attempts to correlate physiological data on preadolescent boys with their maximal oxygen uptake. The data are given in the table below.

Maximal

O2 Uptake

y

Age

(years)

x1

Height

(cm)

x2

Weight

(kilos)

x3

Chest Depth

(cm)

x4

1.54 8.4 132.0 29.1 14.4
1.74 8.7 135.5 29.7 14.5
1.32 8.9 127.7 28.4 14.0
1.50 9.9 131.1 28.8 14.2
1.46 9.0 130.0 25.9 13.6
1.35 7.7 127.6 27.6 13.9
1.53 7.3 129.9 29.0 14.0
1.71 9.9 138.1 33.6 14.6
1.27 9.3 126.6 27.7 13.9
1.50 8.1 131.8 30.8 14.5

We want to use this data to extrapolate the maximal oxygen uptake for a boy with the following characteristics:

Age

(years)

x1

Height

(cm)

x2

Weight

(kilos)

x3

Chest Depth

(cm)

x4

10 137 33.5 14.5

The data were entered in a worksheet with the row and column arrangement shown here and a constant value of True. The TREND function returned a value of 1.66295.

See Also GROWTH, INTERCEPT, LINEST, LOGEST, SLOPE