How does TREND differ from FORECAST.LINEAR?

TREND fits a linear regression to multiple data points and can return an entire array of predictions, making it ideal for analyzing relationships. FORECAST.LINEAR (or FORECAST) predicts a single value. TREND is more flexible for bulk forecasting and understanding the overall trend shape.

Can TREND forecast exponential or curved growth patterns?

No—TREND only models straight-line relationships. If your data follows an exponential or S-curve pattern (common in growth scenarios), use the GROWTH function instead, or transform your data logarithmically before TREND.

What does setting const=FALSE actually do?

const=FALSE forces the regression line to pass through the origin (0, 0), calculating only the slope. const=TRUE (default) fits both slope and intercept. Use FALSE only when a theoretical zero-crossing is mandatory (rare in HR salary data).

Why does my TREND prediction change when I add or remove a data point?

TREND recalculates the entire best-fit line whenever the input data changes, shifting the slope and intercept. This is correct behavior—the trend adapts to reflect your full dataset. Outliers or new hires can noticeably influence the line.

TREND function

Returns predicted y-values using linear regression on known data points, fitting a straight line to extend or interpolate values.

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

Generate a TREND formula

Describe what you need. The generator will reach for TREND where TREND is the right tool, and tell you when it is not.

Arguments

How TREND reads its arguments

Argument	Required	Description
known_ys	Required	Required. A single-row or single-column array of historical y-values (e.g., salaries). Length must match known_xs if provided.
known_xs	Optional	Optional. Array of x-values corresponding to known_ys (e.g., years of service). If omitted, assumed to be 1, 2, 3, …, n. Must be same size as known_ys.
new_xs	Optional	Optional. Array of x-values at which to predict y-values. If omitted, returns fitted values at the original known_xs positions.
const	Optional	Optional. TRUE (default) includes an intercept term; FALSE forces the regression line through the origin. Use FALSE only if zero-crossing is theoretically required.

Returns

An array of numeric values representing the regression-fitted y-values (or predictions at new x-values).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Predict salary for an employee with 10 years of service

Years Employed	Salary
4	57000
5	64000
5	64000
6	71000
7	78000
8	85000

=TREND({57000;64000;64000;71000;78000;85000},{4;5;5;6;7;8},{10})

Result: 99400

TREND fits a line to the known salary-vs-years data, showing approximately $7,700 salary increase per year. For x=10, the line extends to predict ~$99,400. This is useful for compensation planning when estimating veteran employee salaries.

2. Generate fitted trend-line salary for current employees

Years Employed	Actual Salary	Fitted by TREND
4	57000	59200
5	64000	66400
5	64000	66400
6	71000	73600
7	78000	80800
8	85000	88000

=TREND({57000;64000;64000;71000;78000;85000},{4;5;5;6;7;8})

Result: {59200;66400;66400;73600;80800;88000}

Omitting new_xs returns an array of fitted values at each original x-position. This shows what the linear trend line predicts for each employee's current tenure level, revealing salary anomalies (e.g., Eva at 5 years earning $64k vs. the trend's $66.4k suggests she's slightly underpaid on the trend).

3. Estimate starting salary offer for a candidate with 3 years of prior experience

Years Employed	Salary
4	57000
5	64000
5	64000
6	71000
7	78000
8	85000

=TREND({57000;64000;64000;71000;78000;85000},{4;5;5;6;7;8},{3})

Result: 52000

TREND extrapolates backward when new_xs is less than the minimum known_x (here, 3 < 4). Based on the $7,700/year trend, a candidate with only 3 years of experience would align to ~$52,000. This grounds salary offers in the established organizational progression.

Common errors

Which TREND error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	known_ys and known_xs have different dimensions—e.g., 6 salary values but only 5 year values.	Ensure both arrays contain exactly the same number of elements before passing them to TREND.
#DIV/0!	All known_xs values are identical (e.g., {5;5;5;5}), so the regression cannot calculate a slope.	Provide at least two different x-values in known_xs to establish a trend direction.
#REF!	A range reference in known_ys or known_xs points to deleted cells, an invalid worksheet, or out-of-bounds range.	Update the cell references to valid ranges containing your current data, or use named ranges to track changes.

Tips and when to use something else

TREND assumes a strictly linear relationship. If your data curves or zigzags unpredictably, the predictions will drift from reality—plot your data first to verify linearity.
TREND is powerful for extrapolation (predicting beyond your known range) but less reliable the farther you extend beyond the data's bounds.
If you only need the regression slope or R² statistic, use a dedicated function like LINEST (outside the provided list) rather than reverse-engineering from TREND's output.
Combine TREND with IFERROR to handle edge cases gracefully: =IFERROR(TREND(...), "Insufficient data variation") prevents crashes when x-values lack variance.

Related functions

INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.IFERRORIFERROR returns the result of a formula unless that formula generates an error, in which case it returns a user-specified alternative value.AVERAGEAVERAGE calculates the arithmetic mean of numbers, ignoring text and logical values; returns a single numeric value representing the average.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.

Frequently asked questions

How does TREND differ from FORECAST.LINEAR?: TREND fits a linear regression to multiple data points and can return an entire array of predictions, making it ideal for analyzing relationships. FORECAST.LINEAR (or FORECAST) predicts a single value. TREND is more flexible for bulk forecasting and understanding the overall trend shape.
Can TREND forecast exponential or curved growth patterns?: No—TREND only models straight-line relationships. If your data follows an exponential or S-curve pattern (common in growth scenarios), use the GROWTH function instead, or transform your data logarithmically before TREND.
What does setting const=FALSE actually do?: const=FALSE forces the regression line to pass through the origin (0, 0), calculating only the slope. const=TRUE (default) fits both slope and intercept. Use FALSE only when a theoretical zero-crossing is mandatory (rare in HR salary data).
Why does my TREND prediction change when I add or remove a data point?: TREND recalculates the entire best-fit line whenever the input data changes, shifting the slope and intercept. This is correct behavior—the trend adapts to reflect your full dataset. Outliers or new hires can noticeably influence the line.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17