What does the TREND Function do?

stack of neatly arranged blue jeans

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.

In this post, you will learn about what the purpose of the TREND function is, its formula syntax and key conditions with its use.

The TREND function is a statistical function which gets values within a linear trend. It sets a straight line (applying the method of least squares) to the array’s known_y’s and known_x’s. TREND captures the y-values over the course of that line for the array of new_x’s that you clarify.

Use TREND to predict revenue performance for months 13-17 when you have actuals for months 1-12.

Note: If you have a current version of Microsoft 365, then you can enter the formula in the top-left-cell of the output range (cell E16 in this example), then press ENTER to approve the formula as a dynamic array formula. Otherwise, the formula must be stated as a legacy array formula by first choosing the output range (E16:E20), enter the formula in the top-left-cell of the output range (E16), then press CTRL+SHIFT+ENTER to verify it. Excel appends curly brackets at the outset and end of the formula for you. For further information on array formulas, see Guidelines and examples of array formulas.

Syntax of the TREND Function

=TREND(known_y’s, [known_x’s], [new_x’s], [const])

The TREND function syntax contains these specific arguments:

known_y’s  – RequiredThe range of y-values you’re already aware of in the relationship y = mx + b
When the array known_y’s is in a single column, then each column of known_x’s is deduced as an individual variable.
If the array known_y’s is in a single row, then each row of known_x’s is translated as an independent variable.
known_x’s  – OptionalAn optional set of x-values that you might already know in the relationship y = mx + b
The array known_x’s can comprise one or greater groups of variables. When only one variable is used, known_y’s and known_x’s can be sets of any shape, provided they have consistent dimensions. However, if multiple variables are used, known_y’s must be a vector (that is, a range with a height of one row or a width of one column).
If known_x’s is skipped, it is presumed to be the array {1,2,3,…} that is the exact size as known_y’s.
new_x’s   OptionalNew x-values for which you seek TREND to return matching y-values.
New_x’s must contain a column (or row) for each separate variable, similarly like known_x’s does. So, if known_y’s is in a single column, known_x’s and new_x’s must have the equal number of columns. If known_y’s is in a single row, known_x’s and new_x’s must have the exact number of rows.
Should you skip new_x’s, it is taken by default to be equal as known_x’s.
If you ignore both known_x’s and new_x’s, they are considered to be the array {1,2,3,…} that is the exact size as known_y’s.
const   OptionalA logical value defining whether to force the constant b to equal 0.
Should const be TRUE or overlooked, b is calculated as usual.
If const is FALSE, b is formatted equal to 0 (zero), and the m-values are revised so that y = mx.

Remarks of the TREND Function

  • For details about how Microsoft Excel fits a line to data, see LINEST.
  • You can rely on TREND for polynomial curve fitting by regressing against the same variable raised to various powers. For example, suppose column A includes y-values and column B has x-values. You can type x^2 in column C, x^3 in column D, and so on, and then regress columns B to D against column A.
  • Formulas that bring back arrays must be typed as array formulas with Ctrl+Shift+Enter, unless you have an existing version of Microsoft 365, and then you can simply press Enter.
  • Once inputting an array constant for an argument like known_x’s, apply commas to split values in the same row and semicolons to divide rows.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

Other Statistical Functions Topics

Find the middle value with MEDIAN

Rank data without sorting with RANK

LARGE Function

Return the smallest value in a data set with SMALL

Tabulate blank cells with COUNTBLANK 

Determine frequency distributions with FREQUENCY 

GROWTH function

All statistical functions

Leave a Reply

%d bloggers like this: