When to use Excel SMALL Function
Excel SMALL function can be used to get the Kth smallest value from a range of cells or array. For example, you can get the 3rd smallest value from a range of cells.
What it Returns
It returns the value which is the Kth smallest value from a range of cells.
Syntax
=SMALL(array, k)
Input Arguments
array – the array or range of cells from which you want to fetch the kth smallest value. k – the rank or position (from the bottom) of the number that you want to fetch.
Additional Notes
If the array is empty, SMALL returns the #NUM! error value. If k ≤ 0 or if k is greater than the number of data points, SMALL returns the #NUM! error value. If n is the number of data points in a range, then SMALL(array,1) returns the smallest value, and SMALL(array,n) returns the largest value.
Excel SMALL Function – Examples
Here are six example of using Excel SMALL Function:
#1 Getting the smallest value from a list
If you have a list of numbers (A2:A4) and you want to get the smallest number from the list, you can use the following small function: =SMALL(A2:A4,1) Here the Kth value is 1, which would return the smallest number from the range of numbers.
#2 Getting the second smallest value from a list
To get the second smallest value from the list (A2:A4), use the following formula: =SMALL(A2:A4,2) Since the Kth value in the formula is 2, it returns the second smallest values from the range of numbers.
#3 Using SMALL function when there are blank cells in the range
If there are any blank cells in the range, it is ignored by the small function. For example, in the above example, while we take the range of cells as A2:A5, the blank cell is ignored by the SMALL function while returning the smallest value from this list.
#4 Using SMALL function when there are cells with Text
Similar to blank cells, Excel SMALL function also ignores cells with text, alphanumeric characters, special characters, and logical values.
#5 Using SMALL function when there are cells with Duplicates
If there are duplicates in the range used in the Excel SMALL function, it will treat those duplicates as consecutive values. In the above example, two cells have the value 1. Now when you use the SMALL function to return the smallest and second smallest value, it returns 1 in both cases.
#6 Using SMALL function when there is an Error in the Range
If there is an error in any of the cells used in the Excel SMALL function, it will return an error.
Excel SMALL Function – Video Tutorial
Related Excel Functions:
Excel INT Function. Excel MOD Function. Excel RAND Function. Excel RANDBETWEEN Function. Excel ROUND Function. Excel RANK Function. Excel LARGE Function. Excel MAX Function. Excel MIN Function.
