TREND Function

Returns values along a straight-line trend, for one or many new x values.

Category

Statistical

Available in

All versions; spills in Microsoft 365 and Excel 2021+ (older versions: select the output cells and press Ctrl+Shift+Enter)

Returns

A number or an array

Want to practice TREND in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

TREND fits the same least-squares line as LINEST and returns its y values. Give it new x values to predict, or leave them out to get the fitted value for every known x.

Because it accepts a whole range of new x values and spills the answers, it's the quickest way to extend a straight-line projection. It also works with several x columns.

Syntax

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

Arguments

ArgumentRequired?What it does
known_y'sYesThe y values.
known_x'sNoThe x values. If left out, 1, 2, 3 … is used.
new_x'sNoThe x values to predict for. If left out, the known x's are used.
constNoTRUE or omitted calculates the intercept; FALSE forces it to 0.

Examples

Example 1 — Predict two new spend levels

New spend levels in E2:E3; one formula in F2 spills both predictions.

F2fx=TREND(C2:C9,B2:B9,E2:E3)
ABCDEF
1MonthAd spend ($k)Sales ($k)New spend ($k)Predicted sales
2Jan221949.18
3Feb3251053.29
4Mar533
5Apr428
6May638
7Jun740
8Jul846
9Aug531
=TREND(C2:C9,B2:B9,E2:E3) Explain

The same answers as FORECAST.LINEAR for 9 and 10, without copying a formula.

Example 2 — The fitted line for every month

Leave new_x's out to get the line's value at each known x — handy for charting the trend or calculating residuals.

=TREND(C2:C9,B2:B9) Explain

Spills 20.43, 24.54, 32.75 … next to the actual sales.

Common mistakes

  • #SPILL! Cells below the formula aren't empty.
  • #VALUE! A cell in the known ranges is empty or holds text.
  • Straight lines only. For data that grows by a percentage, use GROWTH.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.