Microsoft Excel provides various types of functions to make the work more convenient. Excel SLOPE function is among one of them. In this article, we will learn about five quick examples of the Excel SLOPE function. To clarify, we’ll go over several examples to show you how to use this function appropriately.
Download Practice Workbook
You can download the practice workbook from here.
Overview of Excel SLOPE Function
The SLOPE function returns the slope of the linear regression line through known y’s and known x’s data points. The rate of change along the regression line is calculated by dividing the vertical distance by the horizontal distance between any two locations on the line.
- Generic Syntax
- Argument Description
|known_y’s||Required||An array or cell range of numeric dependent data points.|
|known_x’s||Required||The set of independent data points.|
The SLOPE function returns a numeric value.
- Available in
Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003.
What is SLOPE?
The SLOPE function in Excel is classified as a statistical function. Calculating the slope of a line using values provided by two coordinates is a common task in statistics. The SLOPE function can be used for not only statistics but also in financial analysis.
The SLOPE equation is used to determine the slope of a linear regression line is given below:
Here, where x and y are the samples means AVERAGE(known_x’s) and AVERAGE(known_y’s).
5 Uses of SLOPE Function in Excel
1. Calculate Slope of a Regression Line Using Excel Slope Function
In this section, we will use the SLOPE function to determine the slope of a regression line. We have a dataset of two columns describing sales value as well as corresponding profit.
We will calculate the slope in cell C11. Let’s go through the steps regarding this solution.
- First, select cell C11. Insert the following formula and press Enter.
- Here, we can see the value of slope in cell C11.
- Now we will create a 2D line chart to visualize the functionality of the SLOPE function.
- Select cell (C5:C9). Go to the Insert tab.
- Then from the dropdown of the line graph, select the first option of the 2-D line section.
- Here we will get a graph like this.
Here, we can see the profit values in the Y-axis. Besides, we want to plot the sales values on the X-axis. So to do this we will take some steps.
- Right-click on the graph and select the option “Select Data”.
- We will see a window like this. Select the Edit option under Horizontal Axis Labels.
- Select Axis label range column (B5:B9) and click on OK.
- So, we can see the sales value along the X-axis.
- Also, we will input the Axis Titles and Legend name from the Chart Elements.
We can also insert a trendline to understand the steps of the slope. To this-
- Do a right-click on the line of the graph. Select Add Trendline option.
- After this step, we can see a trendline like the below image.
- Finally, we will get a figure like the one given below after completing all the steps.
2. Use of Excel SLOPE Function to Calculate Negative Slope
The slope value of our previous example was positive. It can also have a negative value. In this example, we will see an example of a negative slope. In this case, we have a dataset of Budget and Expense. Let’s determine the slope of this dataset.
- Firstly, select cell C11 and insert the following formula:
- Then press Enter. We will see a negative value of slope in cell C11.
- Finally, follow the steps of the previous example to insert a graph. We will get a graph of a line having a negative slope.
3. Pairing SLOPE and INTERCEPT Functions Together
We are discussing the Excel SLOPE function. We can write an equation with the pairing of SLOPE and INTERCEPT functions. To pair these two functions we have to know not only the value of the slope of a dataset but also the value of the intercept part.
- Firstly, select cell C12. Insert the following formula:
- Next, press Enter. We will get the value of the intercept part in cell C12.
- Finally, following the equation of a straight line, we can write a new equation with the value of slope and intercept part. The equation of the line is-
Y = mX + C
Slope = m
Intercept = C
Here, the INTERCEPT function returns the y-axis intersection point of the line using x-axis values as well as y-axis values.
Read More: How to Use INTERCEPT Function in Excel
- How to Use MODE Function in Excel (4 Examples)
- Use VAR Function in Excel (4 Examples)
- How to Use PROB Function in Excel (3 Examples)
- Use Excel STDEV Function (3 Easy Examples)
- How to Use Excel GROWTH Function (4 Easy Methods)
4. Use of Excel SLOPE Function as VBA Function
We can apply the SLOPE function in VBA (Visual Basic for Application) to calculate the slope of a dataset. In this case, we will demonstrate this example using the sales data that we used in previous examples. So, we have given the dataset again in the image below.
First, we will see how to activate the Developer tab. Just follow the instructions given below.
- Go to the File option in the top-left corner of our Excel.
- Next, select the Options.
- Then, a new window will come. Select the option Customize Ribbon from the available options.
- After that, select the Developer option and click OK.
- Finally, we can see the Developer tab in our Excel.
Now we will use the Developer tab to create macro-enabled content. Let’s see how we can do this in the following steps.
- Go to the Developer tab. Select the Visual Basic option.
- Here, a new window will open. From the window select the Insert tab. From the drop-down, select the Module option. We will get a new module named Module-1.
- Select the option Module-1. A blank window will open. Insert the following code in the blank window.
Sub SLOPE_Example() Dim Sales As Range Dim Profit As Range Set Sales = Range("B5:B9") Set Profit = Range("C5:C9") MsgBox Application.WorksheetFunction.SLOPE(Profit, Sales) End Sub
- We will Click on the run option that we can see in the below image. We can press F5 as well to run the code.
- Finally, we can see a message box with the value of the slope.
5. Excel Slope Function Errors
5.1 #N/A! Error While Using Excel SLOPE Function
When the specified array of known x’s and array of known y’s are of different lengths, the SLOPE function throws a #N/A! Error.
We will see this error with the following dataset that we used for previous examples.
- Firstly, we will select cell C10 and insert the following formula:
- Finally, press Enter. We can see that cell C10 is showing #N/A! error. At this time it happens because the value of (C5:C9) is not equal to (B5:B10).
5.2 Excel SLOPE Function giving #DIV/0! Error
The SLOPE function gives #DIV/0! Error in two cases:
- If the variance of the specified known x’s evaluates to zero, the SLOPE function throws the #DIV/0! error.
- Any of the known x’s or known y’s arrays are empty.
Hence, we will calculate the slope of the following dataset in cell C10.
- Select cell C10 and insert the following formula:
- Then, press Enter.
- Finally, we will see an error named #DIV/0!. The reason behind this error is the empty value of cell (B5:B9).
Things to Remember
1. The parameters to the SLOPE function must be numbers or names, arrays, or references to numbers.
2. On the other hand, the Error argument contains text logical or empty cells the values are ignored cells with the value zero are ignored.
In the end, in this article, we have tried to make the excel SLOPE function easier for you. Download the practice workbook added with this article and do practice yourself. If you feel any kind of confusion just comment in the below box.