How to use INTERCEPT Google Sheets formula?

The syntax for the INTERCEPT formula is =INTERCEPT(data_y, data_x), it has 2 required parameters.

INTERCEPT

The INTERCEPT function calculates the point where the line of best fit for a set of data intercepts the y-axis. This function is commonly used in regression analysis to find the constant b in the equation y=mx+b where m is the slope of the regression line.

How to use INTERCEPT formula?
Examples of using INTERCEPT formula
INTERCEPT formula not working?
Similar formulas to INTERCEPT

Usage

Use the INTERCEPT formula with the syntax shown below, it has 2 required parameters:

=INTERCEPT(data_y, data_x)

Parameters:

data_y (required):
An array or range containing the dependent variable data points to be used in the regression analysis.
data_x (required):
An array or range containing the independent variable data points to be used in the regression analysis.

Examples

Here are a few example use cases that explain how to use the INTERCEPT formula in Google Sheets.

Finding the y-intercept of a linear regression line

By using the INTERCEPT function in conjunction with the SLOPE function, it is easy to calculate the coefficients of a linear regression line, including the y-intercept.

Predicting values using linear regression

Once a linear regression line has been calculated, the INTERCEPT function can be used to predict values for the dependent variable based on known values of the independent variable.

Common Mistakes

INTERCEPT not working? Here are some common mistakes people make when using the INTERCEPT Google Sheets Formula:

Incorrect range format

One common mistake is using an incorrect range format for either the data_y or data_x arguments. Make sure the range includes all the cells with the data you want to use and that it follows the correct format.

Data not sorted

Another common mistake is not sorting the data in the correct order for the formula to work. Make sure your data is sorted in ascending or descending order depending on your needs.

Incorrect argument order

A mistake that is easy to make is using the data_x argument before the data_y argument in the formula. Make sure you use the correct order for the arguments.

Missing data

If there are missing data points in your data range, the formula may not work correctly. Make sure all the necessary data is included in your range.

Data not numerical

Make sure that the data in your range is numerical for the formula to work correctly. If there are any non-numerical values, the formula may return an error.

The following functions are similar to INTERCEPT or are often used with it in a formula:

LINEST
The LINEST formula is used to calculate the statistics for a line by calculating the line's slope and y-intercept. It is commonly used to find the best-fit straight line for a given set of data points.
RSQ
The RSQ function returns the square of the Pearson product-moment correlation coefficient, which is a measure of the correlation between two sets of data. This function is commonly used in statistical analysis to determine the strength of a linear relationship between two variables.
SLOPE
The SLOPE formula calculates the slope of the linear regression line that best fits the input data. It is commonly used in statistics to analyze trends and predict future values based on past performance.

Learn More

You can learn more about the INTERCEPT Google Sheets function on Google Support.