# 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 - x_{mean})(y - y_{mean})) / Σ((x - x_{mean})^{2})

Σ represents the sum of the values

x_{mean} and y_{mean} are the means (averages) of the x and y values respectively.

Y-intercept (b): b = y_{mean} - m * x_{mean}

## 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}+78^{2}+88^{2}=29880

Calculate Σ(x-x̄)^{2}

1325000/29880 = 44.34 This is the SLOPE value.

Y-intercept (b): b = y_{mean} - m * x_{mean}

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 = y_{mean} - m * x_{mean}

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 signsUse 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 OscarYou can contact me through this contact form