How to use the INTERCEPT function
What is the INTERCEPT function?
The INTERCEPT function returns a value representing the y-value where a line intersects the y-axis. The line is calculated using the method of least squares based on known x- and y-values.
When to use the INTERCEPT function?
Use the INTERCEPT function when you want to build a linear regression line based on this straight line equation:
y = mx + c.
c is where the line cuts the y-axis
m is the slope of the line and can be calculated using the SLOPE function.
What is a linear regression line?
A linear regression line is a straight line fit to data to model the linear relationship between two variables. But it does not definitively prove causality, it provides a simple fitted model for prediction.
What is a linear equation?
A linear equation is a type of equation that can be written in the form ax + b = 0, where a and b are constants and x is a variable. A linear equation represents a relationship between two quantities that are proportional to each other.
For example, if you have a linear equation that says y = 3x + 4, it means that for every unit increase in x, the value of y increases by 3 units and when x is zero y is 4.
The graph of a linear equation is always a straight line, a linear equation does not involve powers of variables.
INTERCEPT Function Syntax
INTERCEPT(known_y's, known_x's)
INTERCEPT Function Arguments
| known_y's | Required. An array or cell reference to dependent data points. |
| known_x's | Required. An array or cell reference to independent data points. |
INTERCEPT function example
Formula in cell E3:
Zero values are included but text, logical values and empty cells are ignored.
INTERCEPT function not working
The SLOPE function returns
- #N/A! error if there are a different number of values in known_y's and known_x's.
How is the INTERCEPT function calculated?
Equation for the INTERCEPT function:
INTERCEPT function = ȳ - bx̄
Equation for b:
SLOPE function: Σ(x-x̄)(y-ȳ)/ Σ(x-x̄)2
The following chart demonstrates the x- and y-values used in the top image and where the fitted line intersects the y-axis which also corresponds to the value calculated by the INTERCEPT function.
'INTERCEPT' function examples
The following article has a formula that contains the INTERCEPT function.
Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]
'INTERCEPT' function examples
This post explains how to lookup a value and return multiple values. No array formula required.
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Functions in 'Statistical' category
The INTERCEPT function function is one of 74 functions in the 'Statistical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form