For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

Statistical

TREND Function

Fits a least-squares regression line to a range or array and returns the y-values along that line for the x-values you specify.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Syntax

Managers...
Charley Kyd can personally help you to apply the Excel methods in this article to your own organization.

Click here to learn more.
TREND(known_y's, known_x's, new_x's, const)

  • known_y's  Required. The set of values you already know in the relationship:
    y = mx + b.

    • If the array known_y's is in a single row or column, then each row or column of known_x's is interpreted as a separate variable.

  • known_x's  Optional. A set of values you might already know in the relationship:
    y = mx + b.

    • 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.

    • The array known_x's can include one or more sets of variables. If only one variable is used, known_y's and known_x's can be ranges of any shape, as long as they have equal dimensions. If more than one variable is used, known_y's must have a height of one row or a width of one column.

  • new_x's  Optional. New x-values for which you want TREND to return corresponding y-values.

    • If you omit new_x's, it is assumed to be the same as known_x's.

    • If you omit both known_x's and new_x's, they are assumed to be the array [1,2,3,...] that is the same size as known_y's.

    • The new_x's argument must include a row or column for each independent variable, just as known_x's does. So if known-y's is in a single column, known-x's and new_x's should have the same number of columns.

  • const  Optional. A logical value that specifies whether to force the constant b to equal 0.

    • If const is TRUE or omitted, b is calculated normally.

    • If const is FALSE, b is set equal to 0 and the m-values are adjusted so that
      y = mx.

Applies To

Excel 2003 and above.

Examples

You can download this example workbook here, along with all other example workbooks I've completed for this Excel help area.

Example 1:

The formulas in row 3 find the trend for the data over seven months. Because the const argument was omitted, the formula used the default value of TRUE.

The formula for cell B3 is copied to the right as shown in the figure.

In the chart, you can see that the calculated trend follows the data very closely.

Example 2:

The formulas in row 3 find the trend in the data over seven periods. Here, however, the const argument was included.

When you compare the values in row 3 of this example to the values in row 3 of the previous example, you'll see a slight difference. This is because dates aren't evenly spaced as the seven periods are.

Even so, the values are so similar that the two charts look virtually identical.

Example 3:

Row 3 in this example is quite different from row 3 of the preceding examples. This is because the const argument is FALSE.

By setting const to FALSE we change the TREND equation from y = mx + b to y = mx. That is, we force the trend line to have a y-intercept equal to zero.

This chart makes the change in the trend easier to see. In the previous two charts, the red line meets the y axis at about 2. But here, it touches at zero exactly.

Example 4:

This example from the STEYX function, shows the TREND function in use. Here, we add the standard error to each trend value to define the High boundary, and we subtract the standard error to define the Low boundary.

You can find a more complete version of this example of data boundaries here.

 

Other ExcelUser Information

Other Help

 

Charley's SwipeFile charts


Free Excel Dashboards