How to use the SMALL function in Microsoft Excel

Let's look at some Excel Small function examples and explore how to use the Small function as a worksheet function in Microsoft Excel:

Summary

It's slightly more difficult to find the largest or smallest number in a column. =MAX() shows the largest value and =MIN shows the smallest value. But far more difficult is finding the second smallest value or fourth smallest value.

What now?

The SMALL function can be used to return the smallest, second smallest, third smallest and nth smallest values from a range.
For example, if all salespersons are given the same target, we can find out which one achieved the sales target in the shortest time for a given year.
The Small function is a built-in function in Excel that is categorized as a Statistical Function.

Purpose

Get the nth smallest value.

Return value

The SMALL function returns a Smallest numeric value.

What Does It Do ?

This function examines a list of values and picks the value at a user specified position in the list.

Syntax

=SMALL(array,k)

Parameters or Arguments

array - A range from which you want to return the nth smallest value
k - The position from the smallest to return.

Formatting

No special formatting is needed.

Usage notes

• The SMALL Function can be used to find the first, second or the third lowest scores of a test. The SMALL function retrieves numeric values based on their position in a list when sorted by value.
• If array is empty or non-numeric, the SMALL function will return the #NUM! error.
• Text values are ignored by the SMALL in Excel and it only recognizes numerical values.
• When the value of the k argument is less than 1 or greater than the number of values in the given array, the SMALL function will return the #NUM! error.
• When the supplied k argument is non-numeric, the small function will return the #NAME? error.
• The small function would return an error if there is an error value anywhere in your data set or array.

Please note that Microsoft Excel uses “k” instead of “n”. We are using “nth”, because it is more easier to understand the LARGE formula in Excel.

Click here to learn more excel function