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.

**INTERCEPT Function in Excel: Syntax**

**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

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

*y***Similar Readings**

## INTERCEPT Function in Excel: Basic Usage

In this section, we will demonstrate the basic use of the **INTERCEPT** function.

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.

## INTERCEPT Function: 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**

**✍ 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.

**Download Practice Workbook**

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

**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 keeps 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.

Stay with us & keep learning.

## Related Articles

**How to Use AVERAGEIF Function in Excel****How to Use Excel AVERAGEIFS Function****How to Use COMBIN Function in Excel****How to Use CORREL Function in Excel****How to Use Excel COUNT Function****How to Use COUNTA Function in Excel****How to Use Excel COUNTBLANK Function****How to Use COUNTIF Function in Excel****How to Use COUNTIFS Function in Excel****FORECAST Function in Excel****How to Use Excel FREQUENCY Function****How to Use LARGE Function in Excel****How to Use LINEST Function in Excel****How to Use Excel MAX Function****How to Use MEDIAN Function in Excel****How to Use MIN Function in Excel****How to Use Excel NORMDIST Function****How to Use NORMINV Function in Excel****How to Use PERCENTILE Function in Excel****How to Use PERMUT Function in Excel****Use of PERMUTATIONA Function in Excel****How to Use PROB Function in Excel****How to Use QUARTILE Function in Excel****How to Use Excel RANK Function****How to Use Excel SLOPE Function****How to Use SMALL Function in Excel****Use Excel STDEV Function****How to Use TREND Function in Excel****How to Use TTEST Function in Excel**