What Is INTERCEPT Function In Excel?
The INTERCEPT Excel function evaluates the point at which a line will intersect the y-axis using existing x-values and y-values. The INTERCEPT function is an inbuilt Statistical function, so we can insert the formula from the “Function Library” or enter it directly in the worksheet.
For example, we will apply the INTERCEPT function to calculate the output of two sets of values in columns A and B.
Select cell B5, enter the formula =INTERCEPT(A2:A4,B2:B4), and press “Enter”. The result is ‘3.071’, also, the graphical representation is shown below.
Table of contents
Key Takeaways
- The INTERCEPT Excel function calculates the intersecting point of a line using the values of x and y, when the selected array is same in size.
- In a selected cell range, if one of the x-value and its corresponding y-value is blank, we will get an INTERCEPT result, however, we cannot plot a graph.
- An argument containing text, logical values, or empty cells are ignored while calculating the INTERCEPT.
- If one of the cell values is 0, it is still included in the calculation.
INTERCEPT() Excel Formula
The syntax of the INTERCEPT Excel formula is,
The arguments of the INTERCEPT Excel formula are,
- known_ys: It is a mandatory argument. These are the “known_ys” values to calculate the intersection.
- known_xs: It is a mandatory argument. These are the “known_xs” values to calculate the intersection.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How To Use INTERCEPT Excel Function?
We can use the INTERCEPT Excel function in 2 ways, namely,
- Access from the Excel ribbon.
- Enter in the worksheet manually.
Method #1 – Access from the Excel ribbon
Choose an empty cell for the output → select the “Formulas” tab → go to the “Function Library” group → click the “More Function” option drop-down → click the “Statistical” option right arrow → select “INTERCEPT” function, as shown below.
The “Function Arguments” window appears. Enter the arguments in the “Known_ys”, and the “Known_xs” fields → click “OK”, as shown below.
Method #2 – Enter in the worksheet manually
- Select an empty cell for the output.
- Type =INTERCEPT( in the selected cell. [Alternatively, type =I or =IN and double-click the INTERCEPT function from the list of suggestions shown by Excel.]
- Enter the arguments as cell values or Excel cell references and close the brackets.
- Press the “Enter” key.
Lets’s take a basic example to learn its application.
We will calculate the output using INTERCEPT Excel function for the value of x and y.
In the table, the data is,
- Column A contains the ‘x’ values.
- Column B contains the ‘y’ values.
The steps to calculate the regression using INTERCEPT formula are,
- Select cell B7, and enter the formula =INTERCEPT(B2:B6, i.e., the value of the known_ys.
- Enter the value of the known_xs argument as “A2:A6”, and close the brackets. The complete formula is =INTERCEPT(B2:B6,A2:A6)
- Press the “Enter” key. The result is “6.6”, and the graphical representation is shown below.
Examples
We will understand some advanced scenarios using the INTERCEPT Excel function examples.
Example #1
We will calculate the output using the INTERCEPT function with a line graph for the value of x and y.
In the table, the data is,
- Column A contains the ‘x’ values.
- Column B contains the ‘y’ values.
The procedure to calculate the regression by the INTERCEPT formula is,
Select cell B7, enter the formula =INTERCEPT(B2:B6,A2:A6), and press the “Enter” key. The calculated result is “-0.08”, as shown in the following image with a line graph.
Example #2
We will calculate the output using the INTERCEPT function for the monthly sales & quantity.
In the table, the data is,
- Column A contains the Months.
- Column B contains the Sales.
- Column C contains the Quantity.
The procedure to calculate the regression by the INTERCEPT formula is,
Select cell C14, enter the formula =INTERCEPT(B2:B13,C2:C13), and press the “Enter” key. The calculated result is “-47.582”, as shown below, also the graphical representation.
Example #3
We will calculate the output using the INTERCEPT function for the time & distance values.
In the table, the data is,
- Column A contains the Time in Seconds.
- Column B contains the Distance in Meters.
The procedure to calculate the regression by the INTERCEPT formula is,
Select cell C14, enter the formula =INTERCEPT(A2:A5,B2:B5), and press the “Enter” key. The calculated result is “4.7052”, as shown below, also the graphical representation.
Important Things To Note
- The “#N/A!” error occurs when the argument’s cell range is not of the same size.
- The arguments consider numbers or names, arrays, or cell references that contain numeric values.
- The “#DIV/0!” error occurs when there is only one x and y-value in a given dataset.
Frequently Asked Questions (FAQs)
The INTERCEPT Excel function is a regression line plotted as a result of the intersection based on known x and y-values. It calculates the value of the dependent variable when the independent variable is equal to zero.
Syntax of the INTERCEPT function is =INTERCEPT(known_ys,known_xs)
We will calculate the output using the INTERCEPT() for the time & distance values.
In the table, the data is,
• Column A contains the known_ys.
• Column B contains the known_xs.
The procedure to calculate the regression by the INTERCEPT formula is,
Select cell C14, enter the formula =INTERCEPT(A2:A4,B2:B4), and press the “Enter” key. The calculated result is “-219.576”, as shown below, also the graphical representation. 
We can insert the INTERCEPT function as follows:
1. Select an empty cell for the output.
2. Type =INTERCEPT( in the selected cell. [Alternatively, type =I or =IN and double-click the INTERCEPT function from the list of suggestions shown by Excel.]
3. Enter the arguments as cell values or cell references and close the brackets.
4. Press the “Enter” key.
For example, the image depicts the time & distance values in columns A & B. We will calculate the output using the INTERCEPT function.
In the table, the data is,
• Column A contains the known_ys.
• Column B contains the known_xs.
The procedure to calculate the regression by the INTERCEPT formula is,
Select cell C14, enter the formula =INTERCEPT(A2:A4,B2:B4), and press the “Enter” key. The calculated result is “7.1129”, as shown below, also the graphical representation. 
The INTERCEPT function is found in the “Formulas” tab, as shown below.
Choose an empty cell for the output → select the “Formulas” tab → go to the “Function Library” group → click the “More Function” option drop-down → click the “Statistical” option right arrow → select “INTERCEPT” function, as shown below.
Download Template
This article must help understand the INTERCEPT Excel function’s formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to INTERCEPT Excel Function. Here we explain how to use INTERCEPT formula along with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply