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 - 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 (c): c = ymean - m * xmean
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 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