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:

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: