Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- Date and Time Function in Excel
- Statistical Function in Excel
SLOPE 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 MS SLOPE in excel 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 formula in excel has two compulsory parameters i.e. known_y’s and known_x’s.
- 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 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?
SLOPE function in excel is very simple and easy to use. Let understand the working of SLOPE excel function by some examples. SLOPE excel can be used as worksheet function and as VBA function.
SLOPE in Excel as worksheet function.
SLOPE in Excel Example #1
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:
SLOPE in Excel Example #2
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 output will be 0.11 as shown in the below table.
SLOPE in Excel can be used as a VBA function.
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 About the SLOPE Function in Excel
- SLOPE Excel 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 in excel =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 in Excel if any 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 excel the parameters must be numbers or names, arrays, or references that contain numbers.
You can download this SLOPE Function in Excel template here – SLOPE Function Excel Template
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 –