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.
Table of Contents
1. Introduction
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.
How to calculate a regression line?
A regression line is based on the method of least squares, which aims to find the line that best fits a set of data points by minimizing the sum of the squared differences between the actual data points and the corresponding points on the line.
We discussed the straight line equation above:
y = mx + c.
- c is where the line cuts the y-axis
- m is the slope of the line
To find the values of m and c that best fit the data we use the following formulas:
Slope (m): m = Σ((x - xmean)(y - ymean)) / Σ((x - xmean)2)
Σ represents the sum of the values
xmean and ymean are the means (averages) of the x and y values respectively.
Y-intercept (b): b = ymean - m * xmean
2. INTERCEPT Function Syntax
INTERCEPT(known_y's, known_x's)
3. 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. |
4. INTERCEPT Function Example 1
Determine the intercept of a linear regression line for the following data? X Values: 10, 15, 20, 25, 30 Y Values: 25, 30, 35, 40, 45
The image above shows the data in cell range B23:C27, the x y scatter chart above shows the data points. The formula in cell E3 calculates the intercept value based on the x and y values in B23:C27.
Formula in cell E3:
INTERCEPT function = ȳ - bx̄
The intercept value is where the regression line intersects the y-axis. The regression line is calculated using the INTERCEPT and SLOPE function, the black line shown in the chart above represents the regression line.
In this particular example, all data points are on the regression line meaning they are not scattered below and above the regression line.
SLOPE function: Σ((x-x̄)(y-ȳ))/ Σ(x-x̄)2
Zero values are included but text, logical values and empty cells are ignored.
5. INTERCEPT Function Example 2
Find the intercept of the line that best fits the following sales data? Units Sold: 80, 170, 190, 280, 290 Revenue ($): 5000, 7500, 10000, 12500, 15000
The image above shows the data in cell range B23:C27, the x y scatter chart above shows the data points. The formula in cell E3 calculates the intercept value based on the x and y values in B23:C27.
Formula in cell E3:
INTERCEPT function = ȳ - bx̄
The intercept value is where the regression line intersects the y-axis. The regression line is calculated using the INTERCEPT and SLOPE function, the black line shown in the chart above represents the regression line.
SLOPE function: Σ((x-x̄)(y-ȳ))/ Σ(x-x̄)2
x̄ = AVERAGE(B23:B27) equals 202
ȳ = AVERAGE(C23:C27) equals 10000
Calculate x-x̄
80 - 202 = -122
170 - 202 = -32
190 - 202 = -12
280 - 202 = 78
290 - 202 = 88
Calculate y-ȳ
5000 - 10000 = -5000
7500 - 10000 = -2500
10000 - 10000 = 0
12500 - 10000 = 2500
1500 - 10000 = 5000
Calculate (x-x̄)(y-ȳ)
-122*-5000=610000
-32*-2500=80000
-12*0=0
78*2500=195000
88*5000=440000
Calculate Σ((x-x̄)(y-ȳ))
610000+80000+0+195000+440000=1325000
Calculate Σ(x-x̄)2
(-122)2+(-32)2+(-12)2+782+882=29880
Calculate Σ(x-x̄)2
1325000/29880 = 44.34 This is the SLOPE value.
Y-intercept (b): b = ymean - m * xmean
b = 10000 - 44.34 * 202 = 1042.50334672021 which is the INTERCEPT value. This value matches the value in cell C17.
6. INTERCEPT Function Example 3
Estimate the fixed cost component based on the following production data? Output (units): 1000, 1200, 1400, 1600, 1800 Total Cost ($): 19600, 22450, 25130, 25340, 29670
The image above shows the data in cell range B23:C27, the x y scatter chart above shows the data points. The formula in cell E3 calculates the intercept value based on the x and y values in B23:C27.
Formula in cell E3:
INTERCEPT function = ȳ - bx̄
The intercept value is where the regression line intersects the y-axis. The regression line is calculated using the INTERCEPT and SLOPE function, the black line shown in the chart above represents the regression line.
SLOPE function: Σ((x-x̄)(y-ȳ))/ Σ(x-x̄)2
x̄ = AVERAGE(B23:B27) equals 1400
ȳ = AVERAGE(C23:C27) equals 24438
Calculate x-x̄
1000-1400=-400
1200-1400=-200
1400-1400=0
1600-1400=200
1800-1400=400
Calculate y-ȳ
19600-24438=-4838
22450-24438=-1988
25130-24438=692
25340-24438=902
29670-24438=5232
Calculate (x-x̄)(y-ȳ)
-400*-4838=1935200
-200*-1988=397600
0*692=0
200*902=180400
400*5232=2092800
Calculate Σ((x-x̄)(y-ȳ))
1935200+397600+0+180400+2092800=4606000
Calculate Σ(x-x̄)2
-400^2=160000
-200^2=40000
0^2=0
200^2=40000
400^2=160000
160000+40000+0+40000+160000=400000
Calculate Σ((x-x̄)(y-ȳ))/ Σ(x-x̄)2
4606000/400000=11.515
Y-intercept (b): b = ymean - m * xmean
24438-11.515*1400 = 8317
This value matches the calculated value in cell C17.
7. 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.
'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 […]
Table of Contents Create dependent drop down lists containing unique distinct values - Excel 365 Create dependent drop down lists […]
Functions in 'Statistical' category
The INTERCEPT function function is one of 73 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