Home › Functions › Statistical › =TREND()
Statistical All versions ◉ 10 views

=TREND()

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.

Applies To: Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016
=TREND(known_y's, [known_x's], [new_x's], [const])
Hover argument names in the table for details.
Output typeArray
Popularity30 / 100
VolatileNo
Dynamic ArrayNo
Download sample .xlsx ▦ Try in Playground Dedicated file for this function — safe & lightweight · sample data + live formulas
Share:

⬇ Download Sample Workbook

A dedicated .xlsx for =TREND() with Persian sample data & live formulas — open in Excel and practice.

  • Sheet “Sample” — data table + formula in place
  • Sheet “Guide” — syntax, arguments & tips from this page
  • Instant & secure (token + rate limit)
Download TREND.xlsx ~ 8–12 KB · No macros

⇪ Share

Send this function to friends

Quiz this function

%d questions — syntax, example & errors — test yourself!

◧ Arguments

ArgumentTypeRequiredDescription
known_y's Range Required The set of y-values you already know in the relationship y = mx + bIf 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 Range Optional An optional set of x-values that you may already know in the relationship y = mx + bThe 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 Range Optional New x-values for which you want TREND to return corresponding y-valuesNew_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 Logical Optional A logical value specifying whether to force the constant b to equal 0If 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.

▣ Examples

Example 1 =TREND(B2:B13, A2:A13, A14:A16)
Example 2 =TREND(B2:B13, A2:A13)

§ Remarks

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

✦ Tips

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

⇄ Related Functions

=LINEST() The LINEST function calculates the statistics for a line by using the "least squares" method to calculate a straight line that best fits your data, and then returns an array that describes the line. You can also combine LINEST with other functions to calculate the statistics for other types of models that are linear in the unknown parameters, including polynomial, logarithmic, exponential, and power series. Because this function returns an array of values, it must be entered as an array formula. Instructions follow the examples in this article. The equation for the line is: y = mx + b –or– y = m1x1 + m2x2 + ... + b if there are multiple ranges of x-values, where the dependent y-values are a function of the independent x-values. The m-values are coefficients corresponding to each x-value, and b is a constant value. Note that y, x, and m can be vectors. The array that the LINEST function returns is {mn,mn-1,...,m1,b}. LINEST can also return additional regression statistics. =FORECAST.LINEAR() Predicts a future value based on the linear trend of past data =GROWTH() Calculates predicted exponential growth by using existing data. GROWTH returns the y-values for a series of new x-values that you specify by using existing x-values and y-values. You can also use the GROWTH worksheet function to fit an exponential curve to existing x-values and y-values. =FORECAST.ETS() Calculates or predicts a future value based on existing (historical) values by using the AAA version of the Exponential Smoothing (ETS) algorithm. The predicted value is a continuation of the historical values in the specified target date, which should be a continuation of the timeline. You can use this function to predict future sales, inventory requirements, or consumer trends. This function requires the timeline to be organized with a constant step between the different points.

💬 Comments & Q&A 0 approved

🔒 Your comment will be published after review.

Share your thoughts — visible after admin approval

No comments yet — be the first!

✍️ Add comment / question

captcha
🔒 Your comment will be published after review.