Excel Small Function
Let’s suppose you have hosted a game and now you have to eliminate bottom three candidates and the bottom 4 candidate is in danger. In that case, you use the inbuilt Excel SMALL function to find the bottom 3 scores and even look for the 4th smallest score in the data set.
In this tutorial, we will cover all the aspects of the SMALL function, including its definition, syntax, parameter, return value, and various examples.
What is Excel SMALL Function?
“The Small function in Excel returns a numeric value based from the specified range when the values are sorted in ascending order. In other words, can retrieve the ‘nth smallest’ value – 1st smallest value, 2nd smallest value, 3rd smallest value, etc.”
In simple words, the SMALL function returns the nth smallest number from the given data set. This function is completely automatic – you just require to provide a range and an integer for “nth” to determine the ranked value you want. The SMALL function comes under the category of Excel Statistical functions.
The SMALL function accepts two parameters, array and k. Array is defined by array or range of numeric data. The parameter k signifies the smallest position or rank. For example, to return the 5 smallest value in array, supply 4 for k.
Syntax
Parameters
- Array (required): This argument specifies an array of, or a reference to, a list of numbers from which the user want to find nth smallest number.
- nth smallest number (required): This argument specifies the nth smallest number which needs to be found out like 5th smallest number etc.
Return
The Small function in Excel returns the nth smallest number for the specified range.
Points to Remember
- Excel SMALL() function typically ignores blank cells, text values, and Boolean data values (True or False).
- If the specified range of cells holds any non-numeric data, this function will return a #NUM! error.
- People often confusion SMALL function and use it to calculate the rank of the data set. But it only determines the nth smallest value. To find the rank of data values, use the inbuilt RANK function.
Examples:
Example 1: Use SMALL function to find the nth SMALL value from the set of numeric data values given in the below table.
| Array/List of Numbers | nth smallest number | ||
|---|---|---|---|
| 13 | 15 | 3 | 1 |
| 21 | 2 | 1 | 2 |
| -18 | -37 | -32 | 3 |
| 11.5 | 1 | 4.5 | 4 |
To find out the smallest nth value follow the below given steps:
STEP 1: Add an empty column
The first step is to add an empty column besides the ‘nth smallest number’. We have named the helper column as ‘Result’.
It will look similar to the below image:
In this column, we will apply the SMALL function for different rows so to look for the nth SMALL value of various numeric values.
STEP 2: Insert the formula
Move to the second row and start typing the formula = SMALL(
Refer to the below image:
STEP 3: Add the parameters
- Next the function will ask for its first parameter i.e., the required input array for which you wish to calculate the nth small value. So, we will specify the range is C4:E4.
- In the next few steps, we will be dragging this formula. Since the column will be the same and only the row number will change for all data sets, we will use Mixed References for the array range. The value given for the array is the mixed reference $C4:$E4. It means columns C and E are locked, but rows are not. When you drag the formula across the cells, the rows will change as per cells, but the $ will prevent columns from changing their values.
It will look similar to the below image:
- In the 2nd parameter, you are required to specify the nth smallest number that you want to find. Here, we have specified cell.
It will look similar to the below image:
STEP 4: SMALL will return the result
The SMALL ($C4: $E4, D4) formula will return the first smallest number as 3
STEP 4: Drag the formula to other rows to repeat
Put your mouse pointer on the formula cell and scroll it towards the right edge of the rectangular box. You will notice that the pointer will change into ‘+’ icon. Drag the formula using the ‘+’ icon to copy the formula down the cells. Don’t worry because here we have used mixed range reference (covered in the above step) where the column is locked but the row is relative, so the specified row in the formula will change as per the cell .
The SMALL function will return the nth smallest number for all different numbers
Refer to the below image for the resulting output:
Eureka! We have successfully received 1st , 2nd and 3rd smallest value from the given set of data, using the SMALL function.
Example 2: Calculate nth smallest number for the following numbers
| Array/List of Numbers | nth smallest number | ||
|---|---|---|---|
| 3 | -23 | 43 | -1 |
| 87 | 2 | 1 | 2 |
| 8 | 59 | 3 | 3 |
| 1.5 | 0 | 7.65 | 4 |
To find out the smallest nth value follow the below given steps:
STEP 1: Add an empty column
The first step is to add an empty column besides the ‘nth smallest number’. We have named the helper column as ‘Result’.
It will look similar to the below image:
In this column, we will apply the SMALL function for different rows so to look for the nth SMALL value of various numeric values.
STEP 2: Insert the formula
Move to the next column and start typing the formula. Starting with equals to(=), formula name and an open parenthesis. The formula will be as follows: = SMALL (
Refer to the below image:
STEP 3: Add the parameters
- Next the function will ask for its first parameter i.e., the required input array for which you wish to calculate the nth small value. So, we will specify the range B5:D4.
- In the next few steps, we will be dragging this formula. Since the column will be the same and only the row number will change for all data sets, we will use Mixed References for the array range. The value given for the array is the mixed reference $B5:$D5. It means columns C and E are locked, but rows are not. When you drag the formula across the cells, the rows will change as per cells, but the $ will prevent columns from changing their values.
It will look similar to the below image:
- In the 2nd parameter, you are required to specify the nth smallest number that you want to find. Here, we have specified cell F4. Close the parenthesis The formula will become, =SMALL($B5:$D5, E5)
It will look similar to the below image:
STEP 4: SMALL will return the result
Once done click on the enter button to fetch the output. The SMALL ($B5: $D5, E5) formula will return the #Num error since we don’t have any value in -1 position.
STEP 4: Drag the formula to other rows to repeat
Put your mouse pointer on the formula cell and scroll it towards the right edge of the rectangular box. You will notice that the pointer will change into ‘+’ icon.
Drag the formula using the ‘+’ icon to copy the formula down the cells. Don’t worry because here we have used mixed range reference (covered in the above step) where the column is locked but the row is relative, so the specified row in the formula will change as per the cell. The SMALL function will return the nth smallest number for all different numbers
Refer to the below image for the resulting output:
Example 3: Let’s see what happens if you want to apply the SMALL Function for text values.
| Numbers | nth smallest number | Result | ||
|---|---|---|---|---|
| 3 | -23 | N/A | -1 | #NUM! |
To find the output for text entries, execute the following steps:
STEP 1: Add an empty column
The first step is to add an empty column besides the ‘nth smallest number’. We have named the helper column as ‘Result’.
It will look similar to the below image:
In this column, we will apply the SMALL function for different rows so to look for the nth SMALL value of various numeric values.
STEP 2: Insert the formula and add parameters
- Move to the next column and start typing the formula. Starting with equals to(=), formula name and an open parenthesis. The formula will be as follows: = SMALL (
- The first parameter takes the input array of the nth small value you want to calculate. So, the range is B5:E5, which includes the list of numbers.
- The 2nd parameter takes the nth smallest number as an input. Here, we have specified cell E5.
It will look similar to the below image:
STEP 4: Excel will throw #num! Error
As a result, Excel will throw the #num! error. This signifies that the SMALL function is made specifically to rank numeric data values. Therefore it will throw #num! error stating that there is problem with the array of numbers used in the function.
