SMALL function

The SMALL function in Excel is used to return the k-th smallest value from a dataset or range. It’s particularly useful for finding the smallest, second smallest, third smallest, and so on, from a group of numbers.

Syntax

=SMALL(array, k)

Parameters

1. array (required): The range or array of numeric data from which you want to find the k-th smallest value.
2. k (required): The position (1 for the smallest, 2 for the second smallest, etc.) of the value you want to return.

Key Points

• The array must contain numeric values.
• Non-numeric values in the range are ignored.
• If k is less than 1 or greater than the total number of numeric values in the array, Excel returns a #NUM! error.

Examples

1. Find the Smallest Value

Given the dataset {10, 20, 30, 40, 50} in A1:A5:

=SMALL(A1:A5, 1)

Result: 10

2. Find the 2nd Smallest Value

For the same dataset:

=SMALL(A1:A5, 2)

Result: 20

3. Handle Duplicate Values

Given the dataset {15, 10, 10, 20, 25} in B1:B5:

=SMALL(B1:B5, 2)

Result: 10

• The second smallest value is also 10 due to duplicates.

4. Using a Range with Non-Numeric Values

For the dataset {5, "Text", 15, 10, 20} in C1:C5:

=SMALL(C1:C5, 3)

Result: 15

• The text value is ignored.

5. Error for Invalid k

For the dataset {10, 20, 30, 40, 50} in A1:A5:

=SMALL(A1:A5, 6)

Result: #NUM!

• There is no 6th smallest value in the dataset.

Notes

• Error Handling:
  • #NUM!: Returned if k is less than 1 or greater than the count of numeric values in the array.
  • #VALUE!: Returned if k is non-numeric.
• Dynamic Range: If the data in the range changes, the result updates automatically.
• Sorting Alternative: The SMALL function provides a specific value without the need to sort the data.

Related Functions

• LARGE: Returns the k-th largest value in a dataset.
• MIN: Returns the smallest value in a dataset.
• MAX: Returns the largest value in a dataset.
• RANK: Returns the rank of a value in a dataset.
• PERCENTILE.EXC / PERCENTILE.INC: Finds values at specific percentiles.

The SMALL function is particularly useful in statistical analysis, grading systems, and ranking scenarios, where identifying specific positions in sorted data is necessary without explicitly sorting the data.