Excel Functions Tutorials
- Statistical Function in Excel
- AVERAGE Excel Function
- Average Formula in Excel
- AverageIF in Excel
- AVERAGEIFS Function in Excel
- CORREL Excel Function
- Correlation Matrix in Excel
- Correlation vs Covariance
- COUNT Excel Function
- Count Formula in Excel
- COUNTA Excel Function
- COUNTIF Excel Function
- COUNTIF Formula in Excel
- COUNTIFS Function in Excel
- COUNTIF with Multiple Criteria
- COUNTIF Examples
- FORECAST Excel Function
- Forecast Formula in Excel
- FREQUENCY Excel Function
- Frequency Formula in Excel
- GROWTH Excel Function
- Growth Formula in Excel
- LARGE Excel Function
- LINEST Excel Function
- Linear Regression in Excel
- Lognormal Distribution in Excel
- MAX Excel Function
- Max Excel Formula
- Mean vs Median
- MEDIAN Excel Function
- MEDIAN Formula in Excel
- MIN in Excel
- MODE Excel Function
- NORM.S.INV Function in Excel
- NORMDIST in Excel
- PERCENTILE Excel Function
- Percentile Formula in Excel
- Percentile Rank Formula
- Poisson Distribution in Excel
- P-Value in Excel
- QUARTILE Excel Function
- RANK Function in Excel
- SLOPE Function in Excel
- SMALL Function in Excel
- Standard Deviation in Excel
- Standard Deviation Formula in Excel
- TREND Function in Excel
- T-TEST in Excel
- Chi Square Test in Excel
- Variance vs Standard Deviation
- Weibull Distribution in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- Excel Tips (178+)
- VBA (162+)
SLOPE Function in Excel (Table of Contents)
SLOPE Function in Excel
SLOPE function in Excel is categorized as statistical functions in Excel. In mathematical term ,the SLOPE returns the slope of a line between given data points in known y’s values and known x’s values. The slope of a linear regression line is the vertical distance/the horizontal distance between any of the two points on this line.
The Slope function returns the slope of a regression line based on the data points recognized by known_y_values and known_x_values
SLOPE Formula in Excel
SLOPE has two compulsory parameters i.e. known_y’s and known_xs.
- known_y’s: it is an array of known y-values.
- known_x’s: it is an array of known x-values
Here the length of the known_x’s data array should be the same length as known_y’s data array, and value of the variance of the known x’s values must not be 0.
The SLOPE equation to find out the slope of the linear regression-line is as follows:
where and are the sample means and calculated by average(x values) and average(y values).
How to Use SLOPE Function in Excel?
It is very simple and easy to use. Let understand the working of SLOPE function by some examples. It can be used as a worksheet function and as a VBA function.
In the first example, we have two data sets with the known y’s values and known x’s value.
Now calculate the slope from this data =SLOPE(A3:A22,B3:B22) and output will be 2.7 as shown in the below table.
Output will be:
In the second example, we have month wise data of known y’s value and known x’s value.
So here we can apply the SLOPE formula in excel as we used in the first example =SLOPE(E3:E22,F3:F22)
And the output will be 0.11 as shown in the below table.
SLOPE in Excel VBA
Suppose we have the X’s values located in the excel sheet range from A1 to A10, and Y’s values located in the given excel sheet from range B1 to B10, then we can calculate the SLOPE here by using the below VBA functions
Sub SLOPEcal() // start the slope function scope
Dim x, y as Range //declare the range x and y
set x = Range(“A10:A10”) //set known x’s values to range x.
set y = Range(“B10:B10”)//set known y’s values to range y.
slope = Application.WorksheetFunction.Slope(y, x) set
MsgBox slope // print the slope value in message box.
End sub // End the slope function
Things to Remember
- SLOPE function through the #N/A! Error when the given array of known_x’s and array of known_y’s are of different lengths.
SLOPE Formula =SLOPE(A3:A12,B3:B15)
- SLOPE function through the #DIV/0! error when:
- The variance of the given known_x’s evaluates to zero; or
- Any of the given arrays (known_x’s or known_y’s) are empty.
- In SLOPE function, if an array or reference argument contains text, logical values, or empty cells, the values are ignored; however, cells with the value zero are included.
- In the SLOPE function, the parameters must be numbers or names, arrays, or references that contain numbers.
SLOPE Function in Excel Video
This has been a guide to SLOPE Function in Excel. Here we discuss the SLOPE Formula in excel and how to use SLOPE function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- Functions in Excel VBA
- VBA MsgBox Function Excel
- ANOVA vs Regression
- Print in Excel
- Excel VBA Arrays
- FIND Excel Function
- INT (Integer) Function in Excel
- MONTH Function on Excel
- EOMONTH in Excel
- COS Excel Function
- SIGN Excel Function
- WORKDAY Excel Function