- Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2019, Excel 2016, Excel 2019 for Mac, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Starter 2010.
In this post, you will learn all about working with the SMALL function effectively in Microsoft Excel. We will cover the following:
- Firstly, a summary of this function;
- Secondly, its formula syntax;
- Thirdly, important conditions to remember when using this function; and
- Finally, an example to play around with it in awe of its convenience.
Summary of the SMALL Function
The SMALL function yields the k-th smallest value in a dataset. Employ this function to capture values with a specific unique position in a dataset.
Syntax of the SMALL Function
SMALL(array, k)
The SMALL function syntax contains these listed arguments:
- Array – Required. An array or range of numerical data for which you are looking to discover the k-th smallest value.
- K – Required. The position (from the smallest) in the array or data range to bring back.
Remarks of the SMALL Function
Working with the SMALL function effectively requires close attention to the following conditions:
- If array is empty, SMALL captures the #NUM! error value.
- If k ≤ 0 or if k is greater than the amount of data points, SMALL yields the #NUM! error value.
- If n is the number of data points in array, SMALL(array,1) corresponds to the smallest value, and SMALL(array,n) equals the largest value.
Example
Copy the sample data from the following table, and paste it into cell A1 of a fresh Excel worksheet. For formulas to display results, choose them, press F2, and then press Enter. If you have to at any point, you can extend the column widths to have a complete view of the entire dataset.
| Data 1 | Data 2 | |
|---|---|---|
| 3 | 1 | |
| 4 | 4 | |
| 5 | 8 | |
| 2 | 3 | |
| 3 | 7 | |
| 4 | 12 | |
| 6 | 54 | |
| 4 | 8 | |
| 7 | 23 | |
| Formula | Description (Result) | Result |
| =SMALL(A2:A10,4) | 4th smallest number in first column (4) | 4 |
| =SMALL(B2:B10,2) | 2nd smallest number in the second column (3) | 3 |
Similar Topics – Statistical Functions
Find the middle value with MEDIAN
Rank data without sorting with RANK
Return the largest value in a data set with LARGE
Tabulat blank cells with COUNTBLANK
Determine frequency distributions with FREQUENCY
Build analysis via regression techniques with TREND