SMALL Function in Excel – Finding nth Smallest Number

In our recent blog, we learn about the LARGE function in Excel to find the nth largest number out of an array of numbers. Moving ahead, this tutorial explains the usage of the SMALL function in excel.

This blog would tell you how to use when to use, syntax, arguments, and examples for the SMALL excel function.

When to Use SMALL Function

The SMALL function is good at finding the nth smallest number out of an array of numbers. The function is an in-built statistical function of excel.

It is helpful when you want to know what is the minimum time taken to answer the question of a rapid-fire round. Likewise, you can use this function to return the minimum time taken by an employee to complete the sales target.

How Does SMALL Function Work in Excel ?

The excel SMALL formula uses the below steps to find the nth smallest number out of different numbers.

• Firstly, the function sorts the array of numbers from smallest to largest.
• Then it returns the nth smallest number out of the array, depending on what input paramter you pass.

Syntax and Arguments

=LARGE(array,k)

The following points describe the standard arguments for a SMALL formula in excel.

• array – It specifies the range from where you will look for the nth smallest number.
• k – It is the position of the nth smallest number you are looking for in the array argument

Examples for SMALL Function of Excel

This section of the blog explains the practical examples for the SMALL formula in Excel and how the SMALL function works.

The below image contains information about the time taken by five quiz participants in the tiebreaker round.

The minimum time taken by the participant will be at the first position and then the second smallest time taken by the participant will score the second position.

To get the result for the rapid-fire round, enter the formula for the first and second lowest time taken.

=SMALL(A2:A6,1)

=SMALL(A2:A6,2)

As a result, the formula will respectively return the following values -13 sec and 17 sec.

Explanation – The formula first sorted the times taken by participants from smallest to largest values. You will not see the A to Z sorting on your screen. After that, it picked the number at the position specified by you.

Do Not Miss These Points

• If there are any cells containing any logical value (TRUE or FALSE), text string, or blank cells then it will not bother the result of the SMALL function.

• If you specify the k argument value less than one or more than the total number in the array argument, the SMALL function returns #NUM! error.

Thank you for reading 🙂