TREND

See Also

Returns values along a linear trend. Fits a straight line (using the method of least squares) to the arrays known_y's and known_x's. Returns the y-values along that line for the array of new_x's that you specify.

Syntax

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

Known_y's   is the set of y-values you already know in the relationship y = mx + b.

Known_x's   is an optional set of x-values that you may already know in the relationship y = mx + b.

New_x's   are new x-values for which you want TREND to return corresponding y-values.

Const   is a logical value specifying whether to force the constant b to equal 0.

Remarks

Example

The example may be easier to understand if you copy it to a blank spreadsheet.

How?

  1. Create a blank spreadsheet.
  2. Select the example in the Help topic.

    Selecting example from Help

    Selecting an example from Help

  3. Press CTRL+C.
  4. In the spreadsheet, select cell A1, and press CTRL+V.
  5. To switch between viewing the formula that returns the result and the result in the cell, select the cell and press F2 and then ENTER, or click Commands and Options on the spreadsheet toolbar, click the Formula tab, and look in the Formula in active cell (active cell) box.

The first formula shows corresponding values to the known values. The second formula predicts the next months values, if the linear trend continues.

Month Cost Formula (Corresponding Cost)
1 $133,890 =TREND(B2:B13, A2:A13)
2 $135,000  
3 $135,790  
4 $137,300  
5 $138,130  
6 $139,100  
7 $139,900  
8 $141,120  
9 $141,890  
10 $143,230  
11 $144,000  
12 $145,290  
Month Formula (Predicted Cost)
13 =TREND(B2:B13, A2:A13,A15:A19)
14  
15  
16  
17  

Note   The formula in the example must be entered as an array formula. After copying the example to a blank spreadsheet, select the range C2:C13 or B15:B19 starting with the formula cell. Press F2, and then press CTRL+SHIFT+ENTER. If the formula is not entered as an array formula, the single results are 133953.3333 and 146171.5152.