TREND function

The TREND function returns values along a linear trend. It fits a straight line (using the method of least squares) to the array's known_y's and known_x's. TREND returns the y-values along that line for the array of new_x's that you specify.

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 input the formula in the top-left-cell of the output range (cell E16 in this example), then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range (E16:E20), input the formula in the top-left-cell of the output range (E16), then press CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas.

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

The TREND function syntax has the following arguments:

Argument	Description
known_y's Required	The set of y-values you already know in the relationship y = mx + b If the array known_y's is in a single column, then each column of known_x's is interpreted as a separate variable. If the array known_y's is in a single row, then each row of known_x's is interpreted as a separate variable.
known_x's Optional	An optional set of x-values that you may already know in the relationship y = mx + b 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 be a vector (that is, a range with a height of one row or a width of one column). 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.
new_x's Optional	New x-values for which you want TREND to return corresponding y-values New_x's must include a column (or row) 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 must have the same number of columns. If known_y's is in a single row, known_x's and new_x's must have the same number of rows. 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.
const Optional	A logical value specifying 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 (zero), and the m-values are adjusted so that y = mx.

Argument

Description

known_y's

Required

The set of y-values you already know in the relationship y = mx + b

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

known_x's

Optional

An optional set of x-values that you may already know in the relationship y = mx + b

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 be a vector (that is, a range with a height of one row or a width of one column).
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.

new_x's

Optional

New x-values for which you want TREND to return corresponding y-values

New_x's must include a column (or row) 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 must have the same number of columns. If known_y's is in a single row, known_x's and new_x's must have the same number of rows.
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.

const

Optional

A logical value specifying 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 (zero), and the m-values are adjusted so that y = mx.

For information about how Microsoft Excel fits a line to data, see LINEST.
You can use TREND for polynomial curve fitting by regressing against the same variable raised to different powers. For example, suppose column A contains y-values and column B contains x-values. You can enter x^2 in column C, x^3 in column D, and so on, and then regress columns B through D against column A.
Formulas that return arrays must be entered as array formulas with Ctrl+Shift+Enter, unless you have a current version of Microsoft 365, and then you can just press Enter.
When entering an array constant for an argument such as known_x's, use commas to separate values in the same row and semicolons to separate rows.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

Need more help?

Need more help?

Want more options?

Was this information helpful?

Thank you for your feedback!

Syntax

Remarks

Need more help?

Need more help?

Want more options?

Was this information helpful?

Thank you for your feedback!