nth smallest value

Excel offers some cool ways to analyze data. One such tool is to find the nth smallest value from a data. It can be daunting to find the 2nd or 3rd smallest value manually if you have a large data set. However, Excel can help us to do that very easily. The SMALL function helps us to find the nth smallest value. In this tutorial, we will learn how to find the nth smallest value in Excel.

Figure 1. How to find the Nth Smallest Value

Formula

=SMALL(range,nth)

Process  

Here, we use the SMALL function. It works in an automatic way. From the syntax we see it uses two arguments range and nth. Range represents the range we want to find the nth smallest value for. We need to provide the range and an integer value n to the SMALL function. SMALL will return the desired nth value.

Setting Up Data

The following example contains a children information database. Column A, B and C has the names, IDs and ages during the month of December.

Figure 2. The Sample Data Set

To find the 3rd youngest age, we need to:

• Select cell E5 with our mouse.
• Write the formula =SMALL(C2:D8,3) to E5.
• Press Enter.

Figure 3. Applying the Formula

This will show the 3rd youngest age, 10 in cell F5.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.