The Excel SMALL function is a Statistical formula that returns the smallest value in an array or a range. In this guide, we’re going to show you how to use the Excel SMALL function and also go over some tips and error handling methods.
Supported versions
- All Excel versions
Excel SMALL Function Syntax
SMALL(array, k)
Arguments
array |
An array of numbers to return the k-th smallest value from. |
k |
An integer that specifies the position from the smallest value. |
Examples
Smallest k-th number
=SMALL(Speed,5)
k-th Smallest Date/time
Excel calculates date and time values as integers. Therefore, the Excel SMALL function can also extract the k-th smallest date/time from an array.
=SMALL(ReleaseDate,5)
Summary and Tips
- Use the Excel SMALL function to determine the k-th smallest value in an array or range.
- The SMALL function can work with arrays/ranges, including those that contain date/time values.
- =SMALL(array,1) returns the smallest value. The MIN function works the same way.
- Use the LARGE function to get k-th largest value from an array.
- The SMALL function can be used with other functions for sorting. See How to filter by using a formula in Excel
Issues
- If array is empty, or k does not point to a valid k value, Excel SMALL function will return a #NUM! error.