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 .

Purpose

Return value

Syntax

=STANDARDIZE(x,mean,standard_dev)
  • x - The value to normalize.
  • mean - The arithmetic mean of the distribution.
  • standard_dev - The standard deviation of the distribution.

Using the STANDARDIZE function

The Excel STANDARDIZE function returns a normalized value (z-score) based on the mean and standard deviation. To use the STANDARDIZE function, calculate the mean with the AVERAGE function , and the standard deviation with the STDEV.P function (see below).

In the example shown, the formula in D5 is:

=STANDARDIZE(C5,$G$4,$G$5)

About z-scores / standard scores

A z-score, or standard score, is a way of standardizing scores on the same scale by dividing a score’s deviation by the standard deviation in a data set. The result is a standard score, or a z-score. It measures the number of standard deviations a given data point is from the mean.

A z-score can be negative or positive. A negative z-score indicates a value less than the mean, and a positive z-score indicates a value greater than the mean. The average of every z-score for a data set is zero.

To calculate a z-score, you need to calculate the mean and standard deviation. The formulas in G4 and G5 are, respectively:

=AVERAGE(points)
=STDEV.P(points)

Where “points” is the named range C5:C12.