FORECAST.LINEAR Function

Predicts a value along a straight-line trend.

Category

Statistical

Available in

Excel 2016+, Microsoft 365

Returns

A number

Taught hands-on in Data Analytics · Relationships & Trends module

Overview

FORECAST.LINEAR fits the least-squares straight line through your known points and returns the y value for a new x. It's the same as INTERCEPT + SLOPE × x.

x can be anything numeric: ad spend, headcount, or a period number or date for a time trend. For trends with a seasonal pattern, FORECAST.ETS usually does better.

Syntax

=FORECAST.LINEAR(x, known_y's, known_x's) Explain

Arguments

ArgumentRequired?What it does
xYesThe new x value to predict for.
known_y'sYesThe values you want to predict (for example, sales).
known_x'sYesThe values that drive them (for example, ad spend), the same size as known_y's.

Examples

Example 1 — Sales at $10k of advertising

Ad spend has ranged from $2k to $8k.

E2fx=FORECAST.LINEAR(10,C2:C9,B2:B9)
ABCDE
1MonthAd spend ($k)Sales ($k)MeasureResult
2Jan221At $10k53.29
3Feb325At $6k36.86
4Mar533
5Apr428
6May638
7Jun740
8Jul846
9Aug531
=FORECAST.LINEAR(10,C2:C9,B2:B9) Explain

About $53.3k. $10k is beyond anything in the data, so the further you extrapolate, the less you should trust the line.

Example 2 — Next quarter from a time trend

With quarters numbered 1–12 in A and sales in B, predict quarter 13.

=FORECAST.LINEAR(13,B2:B13,A2:A13) Explain

Use dates as x for daily or monthly data.

Common mistakes

  • y before x. The known y's come before the known x's. Swapping them predicts x from y.
  • #DIV/0! All the known x's are the same.
  • #N/A The ranges are different sizes.

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.