Purpose
Get nth smallest value
Return value
The nth smallest value in the array.
Syntax
=SMALL(array,k)- array - An array or range of numeric values.
- k - Position as an integer, where 1 corresponds to the smallest value.
How to use
The SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the "nth smallest" value (1st smallest value, 2nd smallest value, 3rd smallest value, etc.) from a set of numeric data.
The SMALL function takes two arguments, array and k. Array is an array or range of numeric values. The argument k represents position or rank. For example, to return the smallest value in array, provide 1 for k. To return the fifth smallest value in array, provide 5 for k.
To get nth largest values, see the LARGE function.
Examples
In the formula below, the SMALL function returns the third smallest value in a list of five numbers provided in an array constant:
=SMALL({29,14,33,19,17},3) // returns 19
Note values do not need to be sorted. To retrieve the 1st, 2nd, and 3rd smallest values in a range:
=SMALL(range,1) // 1st smallest value
=SMALL(range,2) // 2nd smallest value
=SMALL(range,3) // 3rd smallest value
In the example shown, the formulas in G5, G6, and G7 are, respectively:
=SMALL(D5:D16,1) // returns 66
=SMALL(D5:D16,2) // returns 69
=SMALL(D5:D16,3) // returns 71
See below for more advanced formulas based on the SMALL function.
Notes
- SMALL ignores empty cells, text values, and TRUE and FALSE values.
- If array contains no numeric values, SMALL returns a #NUM! error.
- To determine the rank of a number in a data set, use the RANK function.
SMALL function examples
Multiple matches into separate columns
In this example, the goal is to get all names in a given group into the same row, in separate columns, as seen in the worksheet. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns using common values, which in this case are the group...
Find lowest n values
The SMALL function retrieves the smallest values from data based on a given rank. For example: =SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest In the worksheet shown, the rank (which is provided to SMALL as the k argument) comes from numbers in column E...
Highlight 3 smallest values with criteria
Inside the AND function there are two logical criteria. The first is straightforward, and ensures that only cells that match the color in E5 are highlighted: $B3=$E$5 The second test is more complex: $C3<=SMALL(IF(color=$E$5,amount),3) Here, we filter amounts to make sure that only values...
Minimum value
In this example, the goal is to get the minimum quiz score (i.e. the lowest quiz score) for each person listed in column B from the five quiz scores that appear in columns C through G. This is a job for the MIN function or the SMALL function, as explained below. MIN function The MIN function...
Sum bottom n values
In this example, the goal is to sum the smallest n values in a set of data, where n is a variable that can be easily changed. At a high level, the solution breaks down into two steps (1) extract the n smallest values from the data set and (2) sum the extracted values. This problem can be solved...
Highlight bottom values
In this example, the goal is to highlight the 5 bottom values in B4:G11 where the number 5 is a variable set in cell F2. This formula uses two named ranges: data (B4:G11) and input (F2). These are for readability and convenience only. If you don't want to use named ranges, make sure you use...
Sort numbers ascending or descending
The SMALL function is meant to extract the "nth" smallest value from a set of data. The value for N is supplied as the second argument. To get the smallest value with SMALL, supply 1, to get the second smallest value, supply 2, and so on. =SMALL(data,1) // 1st smallest =SMALL(data,2) // 2nd...
Biggest gainers and losers
In this example, the goal is to display the biggest 3 gainers and losers in a set of data where Start and End columns contain values at two points in time, and Change contains the percentage change in the values. The data in B5:E16 is defined as an Excel Table with the name data . Two formulas are...
Break ties with helper column and COUNTIF
At the core, this formula uses the COUNTIF function and an expanding range to count occurrences of values. The expanding reference is used so that COUNTIFS returns a running count of occurrences , instead of a total count for each value: COUNTIF($C$5:C5,C5) Next, 1 is subtracted from the result (...
nth smallest value
In this example, the goal is to extract the 3 best race times for each name from the 5 race times that appear in columns C, D, E, F, and G. In other words, for each name listed, we want the fastest time, the 2nd fastest time, and the 3rd fastest time. This problem can be solved with the SMALL...
Multiple matches into separate rows
In this example, the goal is to get all names in a given group into separate rows grouped by column, as seen in the worksheet above. This is sometimes referred to as a "pivot" operation. The idea is to restructure the data into multiple columns where each column holds the names that belong to a...
Sum bottom n values with criteria
In this example, the goal is to sum the smallest n values in a set of data after applying specific criteria. In the worksheet shown, we want to sum the three smallest values, so n is equal to 3. At a high level, this problem breaks down into three separate steps: Apply criteria to select specific...
Get nth match
The goal is to retrieve the nth matching record in a set of data, after filtering on a specific product. In the worksheet shown, the product in H4 and the value for n in H5 are inputs that can be changed at any time. For instance, if the product in H4 is "A" and the value in H5 is 3, the formula...
Get nth match with INDEX / MATCH
The goal is to retrieve the nth matching record in a table when targeting a specific product. For example, if the value in cell H4 is "A", the formula in H7 should return the name "John", since this is the first name in the table associated with product "A". In the same way, the formula in H8...
nth smallest value with criteria
In this example, the goal is to retrieve the lowest 3 scores in column D that appear in a given group, entered as a variable in cell F5. If the group is changed, the formulas should calculate new results. The core of the solution is the SMALL function, which can be used to retrieve the "nth"...
SMALL function videos
How to get nth values with SMALL and LARGE
In this video we'll look at how to calculate the "nth" smallest or largest values in a range using the SMALL or LARGE function s. This would be, for example, the 1st, 2nd, and 3rd smallest or largest values. In this first sheet, we have a list of students with five test scores. I'll use the LARGE...
How to calculate maximum and minimum values
In this video we'll look at how to calculate minimum and maximum values in Excel. Let's take a look. Here we have a list of properties, that includes an address, a price, and a variety of other information. Let's calculate the maximum and minimum values in this list. First, I'm going to create a...
How to show top or bottom n results
In this video, we'll use the FILTER function to show the top or bottom results in a set of data. Here we have some test scores for a group of students. In column F, I want to set up a formula to display the top students by score. Now, I'm going to use the FILTER function, but we'll need a way to...
Related functions
LARGE Function
The Excel LARGE function returns a numeric value based on its position in a list when sorted by value in descending order. In other words, LARGE can retrieve the "nth largest" value – 1st largest value, 2nd largest value, 3rd largest value, etc.
RANK Function
The Excel RANK function returns the rank of a numeric value when compared to a list of other numeric values. RANK can rank values from largest to smallest (i.e. top sales) as well as smallest to largest (i.e. fastest time).
MIN Function
The Excel MIN function returns the smallest numeric value in the data provided. The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.
