Excel TREND Function

by | Apr 12, 2022 | Excel Formulas

Excel TREND Function

TREND

The TREND function is a Statistical function that can return values along a linear trend line through a given set of dependent and, optionally, independent values. In this guide, we’re going to show you how to use the TREND function and go over some tips and error handling methods.

Supported versions

  • All versions

TREND Function Syntax

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

Arguments

known_y's A set of dependent y-values that you already know in the relationship y = mx + b
[known_x's] Optional. One or more sets of independent x-values that you already know in the relationship y = mx + b
[new_x's] Optional. One or more sets of new x-values that you want to calculate corresponding y-values
[const]

Optional. A logical value specifying whether the constant b is equal to 0.

  • TRUE or omitted: b will be calculated normally.
  • FALSE: b will be set equal to 0 (zero), thus the equation become y = mx.

TREND Function Examples

Dynamic Arrays vs Array Formula

The TREND function returns all values in a trend line. Multiple values mean multiple cells, also known as a range. If you are a Microsoft 365 subscriber, you will see that the function populates multiple cells as much as given known-y values at once. This behavior is called dynamic arrays by Microsoft.

If you are not a subscriber, you should select the cells to be populated and use Ctrl + Shift + Enter method to force Excel to fill the selected cells. Regular Enter key will display the first value only.

Trend for Known Values

The only required argument for the TREND function is the known-y values (known_y's). Excel assumes integers starting from 1 as x values.

TREND(known_y's)

If your data has its own set of independent variables, select them as known_x's.

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

Forecasting with TREND Function

You can use the TREND function to forecast future dependent values on a linear basis, similar to FORECAST.LINEAR function. Supply, the independent x values for the future data points as the third argument.

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

Download Workbook

Remarks

  • The TREND function fits a straight line to the given known_y's and known_x's.
  • The straight line equation is either one of the below:
    • y = mx + b
    • y = m1x1 + m2x2 + ... + b
  • The function returns an array of values. If you are not a Microsoft 365 subscriber, use Ctrl + Shift + Enter key combination to enter the formula as an array formula.
  • The return array's dimension is equal to known_x's array.
  • The row count for known_y's and known_x's should be equal. Otherwise, the function returns #REF!.