The SMALL function will return the smallest specified number from a given range or data set. For example, SMALL can return the 2nd smallest value from a column or the 4th smallest value from an entire table.
Contents:
Syntax
= SMALL(array, k)array = a range, dynamic array, table, row, or column to be evaluated
k = the kth smallest number to be return from the array
Explanation
The SMALL function is part of the "Statistical" group of functions within Excel.
This function is different from MIN as SMALL allows you to specify which smallest (1st, 2nd, 3rd... smallest) number you wish to return. MIN will always return the smallest possible value.
To specify which smallest number to return, the k argument must be changed. This argument works in ascending order from the smallest number in the array. For example, k = 1 would return the smallest number in the array while k = 2 would return the second smallest number, 3 being the third smallest number, and so-on.
The array argument can be a variety of inputs. Any cell range, column, row, or table, can be fed into the function.
This function is fully compatible with dynamic ranges which is especially useful as it gives much more flexibility and adds to the capability of the function as shown in the below examples
The LARGE function will do the opposite of SMALL, and return the largest specified value.
Notes:
- If the array contains any blank cells or cells containing text, those cells will be ignored when evaluating the smallest number
- If there are no numerical values, the function will return a #NUM! error
- The k argument must be positive and not be greater than the number of items in the array, if they are, a #NUM! error will be returned
Examples
1. How to Return the Nth Smallest Value in a Column
To return the nth smallest value from an entire column, we can use the SMALL function, and use the whole column as our array.
By referencing the entire column, or C:C in this example, the SMALL function will look at every value in the column and return whichever Nth smallest value we need.
In this example, we are returning the 2nd smallest sales quantity value from the C column.
= SMALL(column, nth_smallest)= SMALL(C:C,F2)
2. How to Return the Nth Smallest Value in a Row
The same principles from the above example can be applied here. To return the Nth smallest value from a row, we can use the SMALL function, and use the whole row as our array.
By referencing the entire row, or 3:3 in this example, the SMALL function will look at every value in the given row and return whichever Nth smallest value we need.
In this example, we are returning the 2nd smallest sales quantity value in row 3.
= SMALL(row, nth_smallest)= SMALL(3:3,F2)
3. How to Return the Nth Smallest Value in a Table
The SMALL function can even use entire tables as its array.
In this example, we have a table named "WeekendScoreTable", which contains a list of players, each with a Saturday and a Sunday score. We can plug this table name into the function for our array argument.
When using the entire table as our array, the function will look at every single value held in the table and return the Nth smallest numerical value. Any non-numerical values will be ignored.
= SMALL(table_reference, nth_smallest)= SMALL(WeekendScoreTable, H3)4. How to Calculate the 1st, 2nd, and 3rd to Last Place Scores
The SMALL function can also be a great way to calculate the bottom few scores from a given list of scores.
= SMALL(score_range, place)= SMALL($C$3:$C$11, E3)We will want to use our scoring data as our array, in this example C3:C11. We also want to make these absolute references so we can quickly copy down the first formula in F3 down to the other cells. If we don't and leave it as C3:C11, the cell range will move down as the formula is copied.
From there, we can reference the places we want to return the score from as or k argument.
This process can be repeated with as many scores and places as needed.
5. How to Use the SMALL Function with Criteria
We can get a little bit more complicated and add on a criteria to the SMALL function. This way the function will only look at and return values that match our set criteria.
To do this, we first need to filter out our data for any value matching our criteria. Let's use the below "ScoreTable" and say we want to pull out the 2nd smallest value from everyone in California.
First we can must use the FILTER function to filter the existing table:
= FILTER(ScoreTable, ScoreTable[State] = "CA")
Now that we have the reduced the table down to only rows where the state is equal to "CA", the next step is to then to reduce this down further to just the scores. We can accomplish this using the INDEX function.
= INDEX(FILTER(ScoreTable, ScoreTable[State] = "CA"),, 3)
In this step we fed our previous filtered data into the INDEX function, and using that function, are able to pull out the 3rd column, as seen by the "3" in place of the [column_num] argument. Everything else can be blank as we only want to pull out the column containing the scores.
The last step is to tack on the SMALL function, using the filtered and indexed data set as our array.
The final product will look something like this:
= SMALL(INDEX(FILTER(array, criteria_array = criteria),, return_column), nth_smallest)= SMALL(INDEX(FILTER(ScoreTable, ScoreTable[State] = "CA"),, 3), 2)
6. How to Use the SMALL Function to Return Adjacent Cell
Sometimes you might not want to return the smallest value, but instead a cell in another column related to that value.
To return adjacent cells using the SMALL function, we need to combine SMALL with both the INDEX and MATCH functions.
Essentially, we are going to use the MATCH function to read which row the nth smallest value is held in our data range, and then feed that row position into the INDEX function to return a value in a different column.
Lets go through the steps. First we start with the MATCH and SMALL function.
= MATCH(SMALL(C3:C11,E3),C3:C11,0) Here we are using the value returned by the SMALL function as our lookup value, and are returning the relative row position of it in our data range (C3:C11). In this case the smallest number is in the 9th row position.
Next we need to use INDEX. Because we want to return the Name and not the Score, and we know that the relative row position of one is directly tied to the position of the other, the 9th score position will always match with the 9th name in the list.
Combine everything into one formula and you get:
= INDEX(adjacent_cells, MATCH(SMALL(value_range,nth_smallest), value_range, 0))= INDEX(B3:B11, MATCH(SMALL(C3:C11,E3), C3:C11, 0))7. How to Use the SMALL Function with Multiple Ranges
We can tack on multiple non-contiguous ranges to the SMALL function, allowing for more flexibility.
In place of a standard range of cells for your array, Excel can read multiple disconnected ranges if they are put inside parentheses.
For example, instead of being limited to one selected range "C3:C5", we can use two separate ranges as our array "(range_1, range_2)" like so:
= SMALL((range_1, range_2, range_3,...),nth_smallest)= SMALL((C3:C5,F3:F5),I2)
Here you can see we have multiple non-consecutive ranges selected:
8. How to Use the SMALL Function While Ignoring Duplicates
If there are any duplicates in your values, you may also want to ignore or strip out any duplicate values as duplicate values could skew the results you're looking for.
This can be accomplished by using the UNIQUE function. By using the UNIQUE function on our data range before using the SMALL function, we will only pass on unique values to the SMALL function.
Lets look at this example. We want to return the 4th smallest number from the list in column B, but don't want to consider duplicate values. If we did, 5 would be returned, which is incorrect.
We first must use the UNIQUE function on our data range. This will give us an array of distinct values.
We can then feed this array into the SMALL function, using the UNIQUE array as our array argument.
Combine them together and you get:
= SMALL(UNIQUE(array), nth_smallest)= SMALL(UNIQUE(B3:B11),E2)