Exceljet

Quick, clean, and to the point

Excel INTERCEPT Function

Excel INTERCEPT function
Summary 

The Excel INTERCEPT function returns the point at which a regression line will intersect the y-axis by based on known x and y values.

Purpose 
Get intercept of linear regression line
Return value 
y-axis intercept value
Syntax 
=INTERCEPT (known_ys, known_xs)
Arguments 
  • known_ys - An array or range of numeric data points (dependent values).
  • known_xs - An array or range of numeric data points (independent values).
Version 
Excel 2003
Usage notes 

The INTERCEPT function returns the point at which a line will intersect the y-axis by based on known x and y values. The intercept point is based on a regression line plotted with known x and y values. A regression line is a line that best fits that known data points.

Use the INTERCEPT function to calculate the value of a dependent variable when the independent variable is zero (0).

Example

In the example shown, the formula in E5 is:

=INTERCEPT(C5:C9,B5:B9) // returns 2

This formula returns 2, based on known_ys in C5:C9, and known_xs in B5:B9.

Equation

The equation for the intercept of the regression line (a) is:

where b is the slope. The formula used by Excel to calculate slope is the same one used by the SLOPE function:

In the example shown, the intercept formula can be manually created like this:

=AVERAGE(C5:C9)-SLOPE(C5:C9,B5:B9)*AVERAGE(B5:B9)

This formula returns the same result as that returned by the INTERCEPT function.

Notes

  • If there is only one set of points, INTERCEPT will return #DIV/0!
  • If the count of known ys is different from known_xs, INTERCEPT returns #N/A
Related functions 
Excel SLOPE function
Excel SLOPE Function
The Excel SLOPE function returns the slope of a regression line based on known y values and known x values. A regression line is a "best fit" line based on known data points.
500 Formulas | 101 Functions

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.