The INTERCEPT function in Google Sheets is a powerful tool used in statistical analysis to determine the point at which a linear regression line intersects the y-axis. This function is particularly useful for predicting values and understanding the relationship between variables in datasets.

Syntax

INTERCEPT(data_y, data_x)

• data_y: An array or range containing dependent variable values.
• data_x: An array or range containing independent variable values.

Example #1

=INTERCEPT(A1:A10, B1:B10)

This function calculates the y-intercept of the linear regression line based on the values in the range A1:A10 for the dependent variable and B1:B10 for the independent variable. For instance, if the line intersects at 5.3, the result would be 5.3.

Example #2

=INTERCEPT(E1:E5, F1:F5)

In this case, the function determines the y-value where the line of best fit intersects the y-axis using the dependent values from E1:E5 and independent values from F1:F5. If the intersection is at 7.2, the output will be 7.2.

Example #3

=INTERCEPT(G1:G12, H1:H12)

Here, the INTERCEPT function is applied to identify the y-intercept using values from G1:G12 and H1:H12. Should the computed intercept be 10.5, the returned value would be 10.5.

Error handling

• N/A: This error occurs when the function cannot compute an intercept due to insufficient data points or if the x-values are all the same.
• VALUE!: This error arises when the input ranges for data_y and data_x do not match in size.
• DIV/0!: You may encounter this error when all values in data_x are identical, causing a division by zero in the calculation.

Conclusion

In summary, the INTERCEPT function in Google Sheets is essential for establishing the starting point of a linear regression model, enabling users to make informed predictions and analyses. By understanding its syntax and potential errors, users can effectively integrate this function into their data-driven projects.