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.
- All versions
TREND Function Syntax
|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|
Optional. A logical value specifying whether the constant b is equal to 0.
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.
If your data has its own set of independent variables, select them as 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.
- 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!.