# How to use the SLOPE function

**What is the SLOPE function?**

The SLOPE function calculates the slope of the linear regression line through coordinates.

#### Table of Contents

## 1. Introduction

**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. The line of best fit minimizes the distance from all data points and allows predicting one variable from the other.

**What is the line equation?**

The linear equation has the following form: y = mx + b

y - dependent data points

x - independent data points

m - slope

b - where the line intercepts the y axis

The image above shows the following equation: y = 2x+5

**What is a negative SLOPE value?**

The linear equation has the following form: y = mx + b

If m < 0 which means m is negative and the graph slopes downwards, this is demonstrated in the image above.

If m > 0 then it is positive and the graph slopes upwards.

**Related functions**

Excel Function and Arguments | Description |
---|---|

LINEST(known_y's, [known_x's], [const], [stats]) | Returns statistics for a linear trend line fit |

INTERCEPT(known_y's, known_x's) | Returns y-intercept of linear regression line |

FORECAST.LINEAR(x, known_y's, known_x's) | Predicts y value on linear trend line for given x |

TREND(known_y's, [known_x's], [new_x's], [const]) | Returns predicted y values for linear trend model |

SLOPE(known_y's, known_x's) | Returns slope of linear regression line |

**What is the difference between the INTERCEPT function and the SLOPE function?**

The INTERCEPT function calculates the b coefficient meaning where the linear equation crosses the y-axis whereas the SLOPE function calculates the m coefficient which is a measure of the slope.

y = mx + b

**What if the slope is 0 (zero)?**

The image above demonstrates the linear function y = 0x +5 which is the same as y = 5 This function describes a perfectly horizontal line on an xy chart.

This means that a linear function y = mx + b with a slope coefficient equal to 0 (zero) is horizontal.

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

## 2. Syntax

SLOPE(*known_y's*, *known_x's*)

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. |

## 3. Example 1

**Determine the slope of a linear regression line for the following data?
Y Values: 10, 30, 50, 60, 90
X Values: 5, 23, 11, 56, 45**

The arguments are:

*known_y's = B3:B7*

*known_x's = C3:C7*

Formula in cell B10:

The SLOPE function returns approx. 1.033 for the calculated regressions line that has the best fit based on the given data points. The slope is larger than 0 (zero) meaning it slopes upwards. The SLOPE function includes zeros but text, logical values and empty cells are ignored.

The image above shows the data points given as blue dots in the chart, the black line represents the calculated regression line. The SLOPE value is calculated like this:

SLOPE function:

Σ((x-x̄)(y-ȳ))/ Σ(x-x̄)^{2}

x̄ is the mean for all x values.

ȳ is the mean based on all y values.

The arithmetic mean for the following y values: 10, 30, 50, 60, and 90 are 10+ 30 + 50 + 60 + 90 = 240. 240/5 = 48.

The arithmetic mean for the following x values: 5, 23, 11, 56, and 45 are 5+23+11+56+45 = 140. 140/5 = 28.

Calculate x-x̄

5-28=-23

23-28=-5

11-28=-17

56-28=28

45-28=17

Calculate y-ȳ

10-48=-38

30-48=-18

50-48=2

60-48=12

90-48=42

Calculate (x-x̄)(y-ȳ)

-23*-38=874

-5*-18=90

-17*2=-34

28*12=336

17*42=714

Calculate Σ((x-x̄)(y-ȳ))

874+90+-34+336+714=1980

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

(-23)^{2}+(-5)^{2}+(-17)^{2}+(28)^{2}+(17)^{2}=529+25+289+784+289=1916

Calculate Σ((x-x̄)(y-ȳ))/ Σ(x-x̄)^{2}

1980/1916 = approx. 1.033

## 4. Example 2

**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 SLOPE value based on the x and y values in B23:C27.

Formula in cell E17:

=SLOPE(C23:C27,B23:B27)

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

This value matches the calculated value in cell C17.

## 5. Why is the 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**.*

## 6. How is the function calculated?

Here is the math fomrula behind the SLOPE function:

Here is the text representation. SLOPE function = Σ(x - x̄)(y - ȳ)/Σ(x - x̄)^{2}

x̄ - arithmetic mean of x coordinates

ȳ - arithmetic mean of y coordinates

### 'SLOPE' function examples

The following article has a formula that contains the SLOPE function.

Have you ever tried to build a formula to calculate discounts based on price? The VLOOKUP function is much easier […]

### Functions in 'Statistical' category

The SLOPE 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