Bhubon Costa
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.
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.
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:
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
- How to Use MODE Function in Excel (4 Examples)
- Use VAR Function in Excel (4 Examples)
- How to Use PROB Function in Excel (3 Examples)
- Use Excel STDEV Function (3 Easy Examples)
- How to Use Excel GROWTH Function (4 Easy Methods)
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.
Step 2:
- Select the range C5:C13 for Independent variable known_xs.
Step 3:
- Press Enter to see the Result.
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.
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.
