Excel functions / Statistics
INTERCEPT function
Where the best-fit straight line crosses the y axis: the predicted y when x is 0.
Syntax
=INTERCEPT(known_y, known_x)
Arguments
known_y: The dependent values: what you want to explain or predict.known_x: The independent values, one for each known_y.
When to use it
INTERCEPT gives the point where the best-fit straight line crosses the y axis: the y value the line predicts when x is 0. Together with SLOPE it describes the whole line, y = intercept + slope × x.
Like SLOPE, it takes y first, then x.
Examples
Baseline sales
A B C D 1 Ad spend Sales 2 10 120 69.4 3 15 150 4 20 168 5 25 205 6 30 222 In D2:
=INTERCEPT(B2:B6, A2:A6)→ 69.4A prediction from the line
A B C D 1 Ad spend Sales 2 10 120 250.7 3 15 150 4 20 168 5 25 205 6 30 222 In D2:
=INTERCEPT(B2:B6, A2:A6)+SLOPE(B2:B6, A2:A6)*35→ 250.7
Common mistakes
- Reading it literally when x = 0 is far away. If the data never go near x = 0 (no month had zero ad spend), the intercept is just where the line lands, not a measured baseline.
- Swapping y and x. INTERCEPT(known_y, known_x): the dependent values come first, as in SLOPE.
INTERCEPT in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: INTERCEPT
- Danish: SKÆRING
- Dutch: SNIJPUNT
- Finnish: LEIKKAUSPISTE
- French: ORDONNEE.ORIGINE
- German: ACHSENABSCHNITT
- Hungarian: METSZ
- Italian: INTERCETTA
- Norwegian: SKJÆRINGSPUNKT
- Polish: ODCIĘTA
- Portuguese (Brazil): INTERCEPÇÃO
- Portuguese (Portugal): INTERCETAR
- Russian: ОТРЕЗОК
- Spanish: INTERSECCION.EJE
- Swedish: SKÄRNINGSPUNKT
- Turkish: KESMENOKTASI
Try INTERCEPT in the playground
Practise it
Related functions
- SLOPE: The slope of the straight line that best fits the data: how much y changes for each 1 added to x.
- FORECAST: Predicts a y value for a new x from the best-fit straight line through known data.
- LINEST: Fits a straight line by least squares and returns its slope(s) and intercept, with the full regression statistics (standard errors, R², F, sums of squares) if you ask. Spills.
- TREND: Values on the best-fit straight line (least squares): fitted values for the known data, or predictions for new x values. Spills.