How to use SLOPE Function in Excel

SLOPE Function is a statistical function in excel. SLOPE Function calculates the slope of a line generated by linear regression. In Excel, Slope is calculated by considering the datum points on a straight line called as Known Xs and Known Ys. In a straight line equation which is formed by using Slope and Intercept where Slope is ratio of rise and run which are there anywhere in the straight line. For example we have created a straight line using some values and we need to find the Slope of that using few or all the used values, this is done using Slope function.

Before going to a brief introduction on the SLOPE Function in excel let us discuss what is SLOPE?

Syntax:= SLOPE(known_y’s, known_x’s)

The SLOPE function syntax has the following arguments:

  1. Known_y’s (required argument) – This is an array or range of numeric dependent data points.
  2. Known_x’s (required argument) – This is the set of independent data points.

Example: Let’s look at some Excel SLOPE function examples and explore how to use the SLOPE function as a worksheet function in Microsoft Excel:

Suppose we are given the following data about the known_x’s and known_y’s:

SLOPE Function in Excel 2 - How to use SLOPE Function in Excel

To calculate the slope of the linear regression line through the known_x’s and known_y’s, we will use the formula below:

Syntax:  =SLOPE(A2:A21,B2:B21)

Result: 1.23940678

SLOPE Function in Excel 1 - How to use SLOPE Function in Excel

Note:

  • The arguments must be either numbers or names, arrays, or references that contain numbers.
  • If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with the value zero are included.
  • If known_y’s and known_x’s are empty or have a different number of data points, SLOPE returns the #N/A error value.
  • The equation for the slope of the regression line is:
    SLOPE Function in Excel - How to use SLOPE Function in Excel
    where x and y are the sample means AVERAGE(known_x’s) and AVERAGE(known_y’s).

  • The underlying algorithm used in the SLOPE and INTERCEPT functions is different than the underlying algorithm used in the LINEST function. The difference between these algorithms can lead to different results when data is undetermined and collinear. For example, if the data points of the known_y’s argument are 0 and the data points of the known_x’s argument are 1:SLOPE and INTERCEPT return a #DIV/0! error. The SLOPE and INTERCEPT algorithm is designed to look for one and only one answer, and in this case there can be more than one answer.

    LINEST returns a value of 0. The LINEST algorithm is designed to return reasonable results for collinear data, and in this case at least one answer can be found.

READ:  How to use BETA.INV Function in Excel

 

Add a Comment

Your email address will not be published. Required fields are marked *