Function: SMALL

by | Apr 24, 2019 | Excel Formulas

excel small function

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)
formula demonstrates how you can find the smallest number in a range. The formula returns the 5th smallest number from the range (78 in this example). For recurring values, all cells will be evaluated by the function. For example, if you sort the numbers in this example, you will get {20; 40; 40; 60; 78; …}. Although, 78 is the 4th largest number, the SMALL function counts both cells containing 40.

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)

Download Workbook

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.