Excel Small FunctionLet'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 4^{th} 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. SyntaxParameters
ReturnThe Small function in Excel returns the nth smallest number for the specified range. Points to Remember
Examples:Example 1: Use SMALL function to find the nth SMALL value from the set of numeric data values given in the below table.
To find out the smallest nth value follow the below given steps: STEP 1: Add an empty columnThe 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 formulaMove to the second row and start typing the formula = SMALL( Refer to the below image: STEP 3: Add the parameters
STEP 4: SMALL will return the resultThe SMALL ($C4: $E4, D4) formula will return the first smallest number as 3 STEP 4: Drag the formula to other rows to repeatPut 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 1^{st} , 2nd and 3^{rd} smallest value from the given set of data, using the SMALL function. Example 2: Calculate nth smallest number for the following numbers
To find out the smallest nth value follow the below given steps: STEP 1: Add an empty columnThe 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 formulaMove 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
STEP 4: SMALL will return the resultOnce 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 repeatPut 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.
To find the output for text entries, execute the following steps: STEP 1: Add an empty columnThe 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
It will look similar to the below image: STEP 4: Excel will throw #num! ErrorAs 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.
Next Topic#
