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

QUARTILE function - The function returns the quartile of a dataset in Excel
QUARTILE.EXC function - The function returns the quartile of a dataset without values 0 and 1 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!

Jessica Tanner

Update 19 May 2020

You should read it

May be interested

HARMEAN function - The function returns the harmonic average of a data set in Excel
harmean function: the function returns the harmonic mean of a dataset, the harmonic mean is the reciprocal of the arithmetic mean of the reciprocals. syntax: harmean (number1, [number2], ...)
How to find the Nth value in Excel
when working with data analysis tables in excel, there will often be a request to find the largest value, the second largest value, or the smallest value, ...
MINA function - The function returns the smallest value in the given numbers including text and logical values in Excel
mina function: the function returns the smallest value in the given numbers including text and logical values. syntax: mina (value1, [value2], ...)
DMIN () function (returns the minimum value by condition) in Excel
dmin () function helps you find and return the smallest value in a column in a list or database that meets the conditions that you give.
AVEDEV function - The function returns the average absolute deviation of data points from their midpoint in Excel
avedev function: the function returns the average absolute deviation between a given data point and the average value of the population. the function is a measure of the variability of a dataset
T.DIST.RT - The function returns the Student's t-distribution on the right in Excel
t.dist.rt: the function returns the student's t-distribution on the right. functions used in testing the hypothesis of small numbers of data sets. support functions from excel 2010 onwards. syntax: t.dist.rt (x, deg_freedom)
How to use the NORMDIST function in Excel - Function that returns the distribution in Excel
the normdist function returns a distribution with a specified mean and standard deviation. the normdist function has applications in statistics, including hypothesis testing.
Usage of Min and Max functions in Excel
min and max functions on excel will use to find the maximum value, the smallest value in the table, or the selected data area.
CHISQ.TEST function - The function returns the independence test in Excel
chisq.test function: the function returns the independence test. the function returns the value from the distribution (({chi ^ 2})) when squared for statistics and the appropriate degrees of freedom. support function from excel 2010 version. syntax: chisq.test (actual_range, expected_range)
T.DIST - The function returns the Student t-distribution on the left in Excel
t.dist: the function returns the student's t-distribution on the left. functions used in testing the hypothesis of small numbers of data sets. support functions from excel 2010 onwards. syntax: t.dist (x, deg_freedom, cumulative)

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

You should read it

May be interested

Office information