Excel Trend Function

Equation for a Straight Line

The Excel Trend Function finds the linear trend by using the least squares method to calculate the line of best fit for a supplied set of y- and x- values.

If there is a single range of x-values, the calculated line satisfies the simple straight line equation:

y = mx + b

where,

  • x is the independent variable;
  • y is the dependent variable;
  • m is the slope (gradient) of the line;
  • b is a constant, equal to the value of y when x = 0.

If there are multiple ranges of x-values, the line of best fit satisfies the following equation:

y = m1x1 + m2x2 + ... + b

where,

  • the x's are the independent variable ranges;
  • y is the dependent variable;
  • the m's are constant multipliers for each x range;
  • b is a constant.

Basic Description

The Excel TREND function calculates the linear trend line through a given set of y-values and (optionally), a given set of x-values.

The function then extends the linear trendline to calculate additional y-values for a further supplied set of new x-values.

The syntax of the function is:

TREND( known_y's, [known_x's], [new_x's], [const] )

Where the function arguments are as follows:

known_y's-An array known y-values.
[known_x's]-

One or more arrays of known x-values.

This is an optional argument which, if provided, should have the same length as the set of known_y's.

If omitted, the set of [known_x's] takes on the value {1, 2, 3, ...}.

[new_x's]-

An optional argument, providing one or more arrays of numeric values representing a set of new x-values, for which you want to calculate the corresponding new y-values.

Each array of [new_x's] should correspond to an array of [known_x's].

If the [new_x's] argument is omitted, it is set to be equal to the [known_x's].

[const]-

An optional logical argument that specifes whether the constant 'b', in the straight-line equation y = mx + b, should be forced to be equal to zero.

If [const] is TRUE (or if this argument is omitted) the constant b is treated normally;

If [const] is FALSE the constant b is set to 0 and the straight line equation becomes y = mx.



The Trend Function as an Array Formula:

If more than one new y-value is to be calculated by the Excel Trend function, the new values will be returned as an array. Therefore, the function must be entered as an Array Formula (see the examples below).

To input an array formula, you need to first highlight the range of cells for the function result. Type your function into the first cell of the range, and press CTRL-SHIFT-Enter.

Go to the Excel Array Formulas page for more details.


Trend Function Examples

Example 1 - Extension of a Simple Straight Line

In the spreadsheet below, the Excel Trend Function is used to extend a series of x- and y-values that lie on the straight line y = 2x + 10. The known x- and y-values are stored in cells A2 - B5 of the spreadsheet, and are also shown in the graph.

Note that, although the points in the example fit exactly along the straight line y = 2x + 10, this is not essential. The Excel Trend function will find the line of best fit for any set of values provided to it.

The Trend function uses the least squares method to find the line of best fit and then uses this to calculate the new y-values for the provided new x-values.

Example of use of the Excel Trend Function

In this example, the values of the [new_x's] are stored in cells A8 - A10 and the Excel Trend function has been used, in cells B8 - B10, to find the corresponding new y-values. The equation for this, as shown in the formula bar, is:

=TREND( B2:B5, A2:A5, A8:A10 )

It is seen that the Trend function in the formula bar is encased in curly braces { }. This indicates that the function has been input as an Array Formula.


Example 2 - Trend Function With Multiple Sets of Known X-Values

Example 2 of use of the Excel Trend Function

In the above spreadsheet on the right, the Excel Trend Function is used to extend a series of y-values that correspond to three sets of x-values.

The known x-values are stored in cells A2 - C6 and the known y-values are stored in cells D2 - D6 of the spreadsheet.

The set of new x-values is stored in cells A9 - C11 and the Excel Trend function, which has been entered into cells D9 - D11, is used to find the corresponding new y-values.

The equation for the Trend function, as shown in the formula bar, is:

=TREND( D2:D6, A2:C6, A9:C11 )

Again, it can be seen that the Trend function in the formula bar is encased in curly braces { }, showing that it has been input as an Array Formula.

Further examples of the Excel Trend function are provided on the Microsoft Office website.


Trend Function Errors

The most common errors from the Excel Trend function are listed in the table below:

Common Errors
#REF!-Occurs if the [known_x's] array has a different length to the known_y's array.
#VALUE!-

Occurs if either:

  • Any of the values in the supplied known_y's, [known_x's] or [new_x's] arrays are non-numeric;
  • The supplied [const] argument is not recognised as a logical value.