How to Use INTERCEPT Function in Excel

In scientific research, it is required to calculate predicted values. Because in market research or data science, prediction is usually more significant. Therefore, the INTERCEPT function is more important here. The INTERCEPT function in Excel is a built-in function that is categorized as a Statistical Function. Using existing x- and y-values, the INTERCEPT function calculates the location at which a line will intersect the y-axis. By plotting a best-fit regression line through known x- and y-values determines the intercept point.

Use INTERCEPT Function in Excel

In this tutorial, we will discuss the INTERCEPT function in Microsoft Excel along with its formula syntax and usage. In addition, we will plot a regression line using the INTERCEPT function to make it visualized.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to the INTERCEPT Function in Excel

Function Objective:

Based on known x and y values, the INTERCEPT function in Excel determines the location where a regression line will intersect the y-axis.

Syntax:

=INTERCEPT(known_y’s, known_x’s)

Arguments Explanation:

Argument Required/Optional Explanation
Known_y’s Required The dependent set of observations or data.
Known_x’s Required The independent set of observations or data.

Return Parameter:

The INTERCEPT function gives you a number. It will return the #N/A error if the known y values and known x values arguments have different numbers of items.

Equation

In a regression model, interpreting the intercept isn’t always as simple as it appears. When all X=0, the intercept is the predicted mean value of Y.

So, let’s start with a single predictor, X, in a regression equation.

Equation of Intercept Function in Excel

If X equals 0 on occasion, the intercept is simply Y’s expected mean value at that point. That’s significant. The intercept has no intrinsic value if X never equals 0. Both of these instances occur frequently in real-world data.

The Intercept Function uses the following equation to calculate the intercept of the linear regression line through a set of given points:

where the slope, b is given by the equation:

Equation of Slope in Excel

and the values of x and y   are the sample means (the averages) of the known x- and y-values.

Read More: How to calculate Average, Median, & Mode in Excel


Similar Readings


INTERCEPT Function: Basic Use and Regression Analysis

In this section, we will demonstrate the basic use of the INTERCEPT function and Regression analysis

Basic Use of the INTERCEPT Function in Excel

Consider the following scenario: You have a data set of some independent values (X) and some dependent values of (Y). But there is no linear relationship between X and Y  like a straight line. Therefore, we will use the INTERCEPT function to calculate the value of a dependent variable when the independent variable is zero (0). The following formula is used here,

=INTERCEPT(B5:B13,C5:C13)

Step 1:

  • Type the INTERCEPT function in the cell C14
  • Select the range B5:B13 for the dependent variable known_ys.

Basic Concept of INTERCEPT Function in Excel

Step 2:

  • Select the range C5:C13 for Independent variable known_xs.

Basic Use INTERCEPT Function in Excel

Step 3:

  • Press Enter to see the Result.

Basic Use of Excel


Regression Analysis

In the spreadsheet below, we will find the point where the linear regression line through the known x’s and known y’s crosses the y-axis.

Generally, the objective of a regression model is to figure out how predictors and responses are related. If this is the case, and X is never equal to 0, the intercept is of no use. It doesn’t tell you anything about X and Y’s relationship. Consider centering X when X never equals 0 but you want a meaningful intercept.

Regression Line in Excel

The intercept now has relevance if you rescale X so that the mean or any other relevant value = 0 (simply remove a constant from X). It’s the average value of Y at the specified X value. From the regression line, we have found that, the intercept point from the regression analysis is relatable to the intercept point found from using the INTERCEPT function.

Read More: How to Use AVERAGE Function in Excel (5 Examples)


✍ Things to Remember

✎  Numbers or names, arrays, or references containing numbers should be used as parameters.

✎  Text, logical values, and empty cells in an array or reference argument are ignored; however, cells with the value zero are included.

INTERCEPT returns the #N/A error value if known ys and known xs have different numbers of data points or no data points.


Conclusion

To conclude, I hope this article has given you some useful information about how to apply the INTERCEPT function in Excel. You should learn at first and then apply all of these procedures. Take a look at the practice workbook and put these skills to the test. Your valuable support keep motivating us to make tutorials like this

If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.

We, The ExcelDemy Team, are always responsive to your queries.

Stay with us & keep learning.


Related Articles

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo