fx
FunctionPedia
Quick Answer

TREND Function

Fits a linear trend and returns predicted y-values (array).

✓ Excel✓ Google SheetsExcel All versions

Syntax

TREND
(known_ys, [known_xs], [new_xs], [const])

Parameters

ParameterDescriptionRequired
known_ysThe dependent (y) values of the known data used to fit the line.Required
known_xsThe independent (x) values of the known data; defaults to 1,2,3,... if omitted.Optional
new_xsThe x-values at which to predict y. If omitted, the known x-values are reused.Optional
constTRUE (or omitted) includes an intercept; FALSE forces the line through the origin.Optional

Basic Example

Predict a single future point

=TREND({2,3,5,7,11},{1,2,3,4,5},6)
Result12.2

The fitted line is y = -1 + 2.2x, so at x=6 the prediction is 12.2 (same as FORECAST).

Advanced Examples

Example 1: Predict several points at once

Project multiple future periods

Supply an array of new x-values to get an array of predictions

=TREND({2,3,5,7,11},{1,2,3,4,5},{6,7})
Result: {12.2, 14.4}
Spills two values: 12.2 at x=6 and 14.4 at x=7. In older Excel, confirm with Ctrl+Shift+Enter; in Microsoft 365 it spills automatically.

Example 2: Force line through origin

Physical quantity with zero intercept

Set const to FALSE

=TREND(B2:B12,A2:A12,C2:C5,FALSE)
Result: Predicted y-values with no intercept
The fitted line passes through (0,0); useful when theory requires a zero intercept.

How TREND Works

TREND performs ordinary least-squares linear regression (y = a + bx) on the known data, then evaluates that line at each value in new_xs. Because it accepts an array for new_xs, it can return many predictions in one formula. Text, logicals, and blanks in the known arrays are ignored.

1
Provide known data
List known y-values and, optionally, known x-values.
2
Provide new x-values
List the x-points you want to predict (a single cell or a range).
3
Enter as array
Type =TREND(known_ys, known_xs, new_xs) and press Enter (or Ctrl+Shift+Enter in legacy Excel).

Important Notes & Limitations

  • Models only a linear relationship; use GROWTH for exponential patterns.

  • known_xs and known_ys must have the same number of points, or #REF! is returned.

  • Returns an array, so in pre-dynamic Excel it must be entered as an array formula and needs enough empty cells to spill into.

Common Errors & Fixes

#REF!known_xs and known_ys differ in size, or new_xs cannot spill into the needed cells.

Fix: Match the known array sizes and ensure output cells are empty.

#VALUE!A non-numeric or mis-shaped argument was supplied.

Fix: Pass numeric, equal-length ranges.

Download Practice File

Practice TREND with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the TREND function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

How is TREND different from FORECAST?
FORECAST returns a single predicted y; TREND can return an entire array of predictions at once.
Does TREND need array entry?
In Microsoft 365 it spills automatically; in older Excel use Ctrl+Shift+Enter.
Can TREND fit through the origin?
Yes, set the const argument to FALSE to force the line through (0,0).