What is INTERCEPT Function in Google Sheets?
The INTERCEPT Function in Google Sheets determines the point where a linear regression line intersects the y-axis. In other words, the intercept is the value of the dependent variable when all the independent variables are zero. It gives insights into the data’s starting point. Calculating the intercept is useful when dealing with data that follows a linear trend. For example, if you have the scatter plot of your company’s growth over the months, the INTERCEPT function helps us to find the growth when time is zero. It gives you a baseline to act upon and understand where your data trends.
For instance, the sheet below has a set of x and y values. Let us find the intercept using this data. Let us enter the following formula to find the intercept on the y-axis
=INTERCEPT({3;1;2;1;3}, {-4;-1;0;1;4}). You get the y-intercept result as 2. This function is especially useful in statistics and financial analysis.
Key Takeaways
- The INTERCEPT Google Sheets function calculates the y-intercept in a linear regression of a dataset when all the independent variables are zero.
- The mathematical formula for the intercept is obtained from the equation of a line, y = mx + c. Here, m is the slope and C is the intercept.
- The syntax of the INTERCEPT function is as follows:
= INTERCEPT(data_y, data_x)
Here, data_y represents an array of known variable values.
data_x represents an array of unknown variable values.
- The intercept is very useful when predicting sales, evaluating performance metrics and analyzing trends as it provides a reliable baseline for comparison.
Syntax
The syntax of the Intercept Function is as follows:
= INTERCEPT(data_y, data_x)
Arguments
- data_y: (mandatory) It represents an array or cell reference of known or dependent variable values (y values).
- data_x: (mandatory) It represents an array or cell reference of unknown or independent variable values (x values).
The arguments can be numbers, arrays, or cell references containing numbers.
It returns the y-intercept of the linear regression line. Remember that the INTERCEPT function assumes that the variables have a linear relationship and finds the best-fit line using the least squares method.
How to Use INTERCEPT Function in Google Sheets?
The Intercept function gives the value of the dependent variable, Y, when all independent variables, Xs, are zero in linear regression. It is very easy to use them in Google Sheets. The INTERCEPT function can be used as follows:
Entering INTERCEPT Manually
Let us enter the X and Y variables in the table below. We must find the intercept of this set using the INTERCEPT function.
Step 1: First, we can enter the x—and y—values in an array form in the INTERCEPT function. Go to the cell where the INTERCEPT function’s result is to be found.
Enter the formula =INTERCEPT( in the selected cell.
Step 2: Highlight the range that contains your y values or dependent variables. Place a comma at the end. Next, highlight the range that contains your X values or independent variables. Close the parentheses.
Step 3: Press Enter. You get 11 in cell C6, which indicates the y-axis intercept value. It is the value at which the line intersects the y-axis when the value of x is zero.
Using the Google Menu Bar
In the menu on top of the sheet, choose the Insert tab and select the option Function. Next, click the Statistical option and select INTERCEPT from the functions available.
Examples
The intercept serves as the starting point for evaluating the effects of the independent variables on the dependent variable. It reflects the portion of the dependent variable that is not influenced by the independent variables included in the model. Using the INTERCEPT Google Sheets function, let us look at some interesting examples.
Example #1
In this Intercept function in Google Sheets example, we calculate the intercept to obtain data in a real-life scenario. Below is the data of a pharmaceutical company that has a new drug and introduced test dosages for patient recovery. The table shows the dosage amount in Column A and the corresponding recovery rate in Column B. Let us calculate the intercept, which will show the expected recovery rate when the dosage is zero.
Step 1: To calculate the recovery rate with zero dosage/without treatment, we must use the INTERCEPT function to determine the y-intercept of the line obtained from the values above. It is equal to the recovery rate in percentage for zero dosage or no treatment.
Use the following formula in cell C5.
=INTERCEPT(B2:B6,A2:A6)
Step 2: Press Enter to obtain the value in cell C5. You get 0.22 or 22%, which indicates that patients will recover at a rate of 22% without receiving any dosage
Example #2 – Analyzing Sales Trends
The INTERCEPT function calculates the intercept, which gives a starting point for evaluating the effects of the independent variables on the dependent variable. It gives us the portion of the dependent variable not influenced by the independent variables in the model. In this example, we analyze the sales trends, which show the expected sales when all marketing efforts are at zero.
Below is a table in which Column A contains the marketing budget values while column B contains the resultant sales values.
Step 1: You must find the intercept for this set of values. Enter the formula to find the intercept in cell C4: =INTERCEPT(B2:B7, A2:A7).
Step 2: Press Enter. You get 7305.34. This is the intercept value, which represents the starting sales value when the marketing budget is zero. It helps gauge the effectiveness of your marketing activities in driving sales above that baseline.
Example #3 – Use INTERCEPT with SLOPE Function
In this interesting example, we have some details of an organization’s sales every month for a year. We will use the SLOPE and INTERCEPT functions in Google Sheets to find the rate of change in sales for every unit of marketing spend and the baseline sales for zero marketing spend, respectively.
The SLOPE function calculates the slope of the linear regression line, showing the change in the dependent variable, sales here, for a unit change in the independent variable, marketing spending.
Step 1: Enter the details in a sheet as shown below.
Step 2: To calculate the slope, enter the following formula:
=SLOPE(C2:C13,B2:B13). Press Enter. You get the following result.
Step 3: To calculate the intercept, enter the following formula:
=INTERCEPT(C5:C14, B5:B14). Press Enter. You get the following result.
With the SLOPE and INTERCEPT functions, you can now predict future sales based on your marketing spend. It helps you make more informed business decisions regarding your budget allocation for marketing efforts and understand the return on investment (ROI) for your campaigns.
Important Things to Note
- If only a single x- and y-value are given to find the intercept, you get the #DIV/0! Error.
- Both the arguments of the INTERCEPT Google Sheets function should have the same number of x and y values to avoid the #N/A error.
- Do not use the INTERCEPT function for non-linear data.
- The intercept helps the baseline of a relationship between two variables. Thus, we can understand how a dependent variable reacts without any changes to the independent variable.
Frequently Asked Questions (FAQs)
One important point to note is that linear regression models are always based on assumptions. Here, we assume a linear relationship between variables, which may not always be possible in real-world scenarios. Also, we need normally distributed data without any outliers for linear regression. Therefore, linear regression may not give accurate results with nonlinear data. For such cases, models like polynomial regression or nonlinear regression may be used.
The following are some of the errors which can occur when using INTERCEPT.
1. #N/A Error – We get this error when the array arguments have a different number of data points or contain no data points.
2. #DIV/0! Error – This error occurs when either x’s or y’s are empty or when the variance of the x values is equal to zero. Also, if the X values are the same, the slope cant be calculated, resulting in a division by zero error.
3. #VALUE! Error – When any of the cells in your range contain non-numeric data, you get this error.
The SLOPE function returns the slope of a linear regression line plotted the X and y values. In other words, the SLOPE function in Google Sheets calculates the slope of the linear regression line, showing the change in the dependent variable, for a unit change in the independent variable. In contrast, the INTERCEPT Excel function finds the y-axis intercept of a line using the given x-values and y-values. It gives the expected value of the dependent variable when the independent variable is zero.
Download Template
This article must help understand INTERCEPT Function in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is INTERCEPT Function in Google Sheets. We learn its syntax & how to use it to find the y-intercept with step-wise examples. You can learn more from the following articles. –
Leave a Reply