The INTERCEPT function takes two arguments, known_ys and known_xs, which should be a range or array of numeric values. The known_ys represent dependent values and known_xs represent independent values. Both arguments must contain the same number values, or INTERCEPT will return #N/A.
Example
Values can be entered directly in INTERCEPT as array constants: Typically, values are supplied as ranges. In the example shown, the formula in E5 is: 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: 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
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
