INTERCEPT Function

Returns where the best-fit straight line crosses the y-axis.

Category

Statistical

Available in

All versions, Microsoft 365

Returns

A number

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

Overview

INTERCEPT fits the least-squares straight line through your points and returns its value when x is 0.

With SLOPE you can predict any y: intercept + slope × x. Be careful reading the intercept on its own when x = 0 is far outside your data.

Syntax

=INTERCEPT(known_y's, known_x's) Explain

Arguments

ArgumentRequired?What it does
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 — Baseline sales with no advertising

The same eight months.

E2fx=INTERCEPT(C2:C9,B2:B9)
ABCDE
1MonthAd spend ($k)Sales ($k)MeasureResult
2Jan221Intercept12.2143
3Feb325Slope4.1071
4Mar533At $10k53.29
5Apr428
6May638
7Jun740
8Jul846
9Aug531
=INTERCEPT(C2:C9,B2:B9) Explain

The line suggests about $12.2k of sales at $0 ad spend — but spend was never below $2k, so treat that as a rough extrapolation. The last row predicts sales for $10k of advertising.

Common mistakes

  • y first, then x. Swapping the ranges fits a different line: =SLOPE(B2:B9,C2:C9) gives about 0.24 (ad spend per $1k of sales), not 4.11.
  • #DIV/0! All the x values are the same, so no line can be fitted.
  • #N/A The two 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.