Slope Function

Purpose

Return value

Syntax

=SLOPE(known_ys,known_xs)

• known_ys - An array or range of numeric data points (dependent values).
• known_xs - An array or range of numeric data points (independent values).

Using the SLOPE function

The SLOPE function returns the slope of a regression line based on known y values and known x values. A regression line is a “best fit” line based on known data points.

The slope of a line is a measure of steepness. Mathematically, slope is calculated as “rise over run”, or change in y over the change in x. For example, if a line has a slope of 2/1 (2), then if y increases by 2 units, x increases by 1 unit.

Example

In the example shown, the formula in E5 is:

=SLOPE(B5:B9,C5:C9) // returns -2

This formula returns -2, based on known_ys in C5:C9, and known_xs in B5:B9.

Equation

In statistics, a best fit line does not normally lie exactly on the known x and y points. The equation used by the SLOPE function in Excel is based on the mean of known x’s and y’s:

For the example shown, this formula can be manually recreated like this:

=SUM((B5:B9-AVERAGE(B5:B9))*(C5:C9-AVERAGE(C5:C9)))/SUM((B5:B9-AVERAGE(B5:B9))^2)

The calculated result from the SLOPE function and the manual formula are the same.

Notes

• If there is only one set of points, SLOPE will return #DIV/0!
• If the count of known_ys is different from known_xs , SLOPE returns #N/A

Purpose

Return value

Syntax

=SMALL(array,k)

• array - An array or range of numeric values.
• k - Position as an integer, where 1 corresponds to the smallest value.

Using the SMALL function

The SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the “nth smallest” value (1st smallest value, 2nd smallest value, 3rd smallest value, etc.) from a set of numeric data.

The SMALL function takes two arguments , array and k . Array is an array or range of numeric values. The argument k represents position or rank. For example, to return the smallest value in array , provide 1 for k . To return the fifth smallest value in array , provide 5 for k .

To get nth largest values, see the LARGE function .

Examples

In the formula below, the SMALL function returns the third smallest value in a list of five numbers provided in an array constant :

=SMALL({29,14,33,19,17},3) // returns 19

Note values do not need to be sorted. To retrieve the 1st, 2nd, and 3rd smallest values in a range:

=SMALL(range,1) // 1st smallest value
=SMALL(range,2) // 2nd smallest value
=SMALL(range,3) // 3rd smallest value

In the example shown, the formulas in G5, G6, and G7 are, respectively:

=SMALL(D5:D16,1) // returns 66
=SMALL(D5:D16,2) // returns 69
=SMALL(D5:D16,3) // returns 71

See below for more advanced formulas based on the SMALL function.

Notes

• SMALL ignores empty cells, text values, and TRUE and FALSE values.
• If array contains no numeric values, SMALL returns a #NUM! error.
• To determine the rank of a number in a data set, use the RANK function .