Related Functions:

The TREND function returnsthe linear trend line through a given set of y-values and a given set of x-values.

Syntax

=TREND(known_y’s,[known_x’s],[new_x’s],[const])

Arguments

Argument Description
known_y’s The set of y-values you already know in the relationship y = mx + b

  If the array known_y’s is in a single column, then each column of [known_x’s] is interpreted as a separate variable
  If the array known_y’s is in a single row, then each row of [known_x’s] is interpreted as a separate variable
[known_x’s] Optional. A set of x-values that you may already know in the relationship y = mx + b

  If provided, [known_x’s] should have the same length as the set of known_y’s.
  If [known_x’s] is omitted, it is assumed to be the array {1,2,3,…} that is the same size as known_y’s
[new_x’s] Optional. New x-values for which you want TREND to return corresponding y-values

  If provided, [new_x’s] should have the same length as the set of known_y’s.
  If omitted, [new_x’s] 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 the array {1,2,3,…} that is the same size as known_y’s
[const] Optional. A logical value specifying whether to force the constant b to equal 0

  TRUE
(or omitted)
b is calculated normally
  FALSE b is set equal to 0 (zero), and the m-values are adjusted so that y = mx

Note: Because TREND returns an array, it must be entered as an array formula. If the formula is not entered as an array formula, a single result will be returned.

Examples

  A B C D E
1 Month Price Result Formula Notes
2 1 $133,631 $134,533 {=TREND(B2:B6,A2:A6)} Formula entered in C2:C6 as an array formula
3 2 $139,879 $138,525    
4 3 $141,285 $142,518    
5 4 $148,527 $146,511    
6 5 $149,271 $150,504    
7          
8 6   $154,497 {=TREND(B2:B6,A2:A6,A8:A11)} Formula entered in C8:C11 as an array formula
9 7   $158,489    
10 8   $162,482    
11 9   $166,475    

Note: The curly brackets, { and }, seen in the formula entered in C2:C6 and C8:C11 are not entered by the user. Excel applies these to show the formula has been input as an array formula.

Common Function Error(s)

Problem What went wrong
#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
#REF! Occurs if the [known_x’s] array has a different length to the known_y’s array

The 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 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 = m_1x_1 + m_2x_2 + \cdots + b    \]