SMALL function - The function returns the kth smallest value in a dataset in Excel

The following article introduces you to the SMALL function - one of the functions in the statistical function group is very popular in Excel.

SMALL function - The function returns the kth smallest value in a dataset in Excel Picture 1

Description: The function returns the kth smallest value in a dataset.

Syntax: SMALL (array, k)

Inside:

- array : The array or range of data points you want to determine the kth smallest value.

- k: The order (from the smallest value) in the data array to be returned.

Attention:

- If array is empty -> the function returns the #NUM! Error value .

- If  k ≤ 0 or k exceeds the number of data points in the array -> the function returns the #NUM! Error value

- where n is the number of data points we have:

+ SMALL (array, 1) -> returns the smallest value in the array.

+ SMALL (array, n) -> returns the largest value in the array.

For example:

Calculate the kth smallest value as described in the following data table:

SMALL function - The function returns the kth smallest value in a dataset in Excel Picture 2

- Calculate the second smallest value in array1. In a cell to calculate enter the formula : = SMALL (C6: C10,2)

SMALL function - The function returns the kth smallest value in a dataset in Excel Picture 3

- Press Enter -> the second smallest value in array1 is:

SMALL function - The function returns the kth smallest value in a dataset in Excel Picture 4

- Calculate the 3rd smallest value in array2. In a cell to calculate enter the formula: = SMALL (D6: D10,3)

SMALL function - The function returns the kth smallest value in a dataset in Excel Picture 5

In array2, there are 2 similar elements are 29 but the function is still arranged in 2 orders, 1 value is the 3rd and the value is 4th.

- Calculate the 6th smallest value in array1. In a cell to calculate enter the formula: = SMALL (C6: C10,6)

SMALL function - The function returns the kth smallest value in a dataset in Excel Picture 6

Because array1 has only 5 elements, there is no 6th value -> the function returns the #NUM! Error value .

Above are instructions and some specific examples when using the SMALL function in Excel.

Good luck!