What Is SMALL Function In Google Sheets?
The SMALL function in Google Sheets finds returns the “nth” i.e., the smallest numerical value from the selected values, unlike the MIN() function only returns the smallest value. The Google Sheets SMALL Function returns the set value of n such as, if the n is set to 1, 2 or 3, then, the return value will be the smallest, 2nd smallest or the 3rd smallest number in the dataset, respectively.
For example, we will find the smallest from the values given below using the SMALL Function in Google Sheets.
Select cell B2, enter the formula =SMALL(A2:A3,1) and press the “Enter” key.
The output is “10”, as shown above. In column A values, the smallest number is returned as the output.
Table of Contents
Key Takeaways
- The SMALL Function in Google Sheets is usedto fetch the smallest, 2nd smallest, 3rd smallest values and so on… values that helps is avoid sorting the values in a dataset into ascending order.
- If there are any duplicate values, the function will consider the first value as the smallest.
- The “nth” value should be numeric and must be provided mandatorily for the formula to execute.
- The value of the array should not be empty. Also, if there are any errors in the selected cells, we will get the result as an error.
- The SMALL Function ignores the text values in the selected cell range.
- We can use SMALL() with IF() and ROW() functions to perform advance calculations.
SMALL() Google Sheets Formula
The syntax of the SMALL Google Sheets Formula is,
The arguments of the SMALL Google Sheets Formula are,
- data: It is a mandatory argument. It is the range or array from which we want the function to return the nth smallest value.
- n: It is a mandatory argument. It is an integer that specifies the position from the value, i.e., the smallest “nth” position.
How To Use SMALL Function In Google Sheets?
We can use the SMALL in Google Sheets in 2 methods, namely,
- Access from the Google Sheets ribbon.
- Enter the worksheet manually.
#1 – Access from the Google Sheets ribbon 🡢
Choose an empty cell for the output 🡢 select the “Insert” tab 🡢 click the “Function” option right arrow 🡢 click the “Statistical” option right arrow 🡢 select the “SMALL” function, as shown below.
The “SMALL” formulaappears, as shown below. Enter the argument as the cell reference.
#2 – Enter the worksheet manually 🡢
- Select an empty cell for the output.
- Type =SMALL( in the selected cell. [Alternatively, type “=S” or “=SM” and double-click the SMALL function from the list of suggestions shown by Google Sheets.]
- Enter the arguments as cell references or direct values.
- Close the parenthesis and press the “Enter” key.
Examples
We will understand the SMALL function in Google Sheets with some advanced scenarios.
Example #1
We will find the 1st and 2nd smallest salaries of the employees from the XYZ company using the Google Sheets SMALL function.
In the table, the data is,
- Column A contains the Name.
- Column B contains the Salary.
The steps to detect the smallest salary using the SMALL in Google Sheets are:
Step 1: First, select cell B6 and enter the formula =SMALL(B2:B5,1).
Step 2: Press the “Enter” key. The output is shown below.
Step 3: Next, select cell B7 and enter the formula =SMALL(B2:B5,2).
Step 4: Press the “Enter” key. The 1st and the 2nd smallest salaries are shown in the image below.
Example #2 – Nesting SMALL With IF
We will find the smallest and the second smallest scores from the two subject scores of a class using the IF() and the SMALL() function.
In the table, the data is,
- Column A contains the Name.
- Column B contains the Scores.
- Column C displays the Output.
- Cells E1:G3 to displays the Output where column E holds the nth values as 1 and 2 in cells E2 and E3, respectively.
The steps to find the smallest scores of the subjects using Google Sheets SMALL Function are:
Step 1: Select cell F2 and enter the formula =small(if(B2:B13=F1,C2:C13),E2)
Step 2: We must remember to execute the formula as an array. Therefore, press the keys “Ctrl+Shift+Enter”, now the formula changes to
=ArrayFormula(small(if(B2:B13=F1,C2:C13),E2))
and then press the “Enter” key.
Step 3: Next, select cell G2, enter the formula =small(if(B2:B13=G1,C2:C13),E2)), press ““Ctrl+Shift+Enter” and press “Enter”.
[Note: The formula is now seen as =ArrayFormula(small(if(B2:B13=G1,C2:C13),E2))]
Step 4: Next, select cell F3, enter the formula =small(if(B2:B13=F1,C2:C13),E3), press ““Ctrl+Shift+Enter” and press “Enter”.
[Note: The formula is now seen as =ArrayFormula(small(if(B2:B13=F1,C2:C13),E3))]
Step 5: Finally, select cell G3, enter the formula =small(if(B2:B13=G1,C2:C13),E3), press ““Ctrl+Shift+Enter” and press “Enter”.
[Note: The formula is now seen as =ArrayFormula(small(if(B2:B13=G1,C2:C13),E3))]
The smallest and the 2nd smallest scores of Maths, are 56 and 67 and of English, are 60 and 73, respectively, as shown in the above image in the cells F2:G3
Example #3 – Sort Using SMALL
We will sort the values and from the smallest value to the largest, i.e., in the ascending format, from the given table array using the SMALL() and the ROW() function.
In the table, the data is,
- Column A contains the Values.
The steps to sort the values in ascending order using the SMALL() and the ROW() functionare:
Step 1: Select cell B2 and enter the formula =small(A2:A10,row()-1)).
Step 2: Press the “Enter” key. The output is ‘0.56’ as shown below.
Step 3: Drag the formula from cell B2 to B10 using the fill handle. We will get the output shown below, but with the “#NUM!” error.
We can fix this error by locking the cell range by making it absolute or by executing it as an array by pressing the keys “Ctrl+Shift+Enter”.
Step 4: Select cell B2, double-click to activate the formula, press “Ctrl+Shift+Enter”, press “Enter” and then drag the formula to the rest of the cells. The formula now becomes =ArrayFormula(small($A$2:$A$10,row()-1)).
We get the output of the values sorted in ascending order, as shown below.
Example #4
We will find the least cost using the SMALL formula in Google Sheets from the stocks of the items of the garments shop.
In the table, the data is,
- Column A contains the Items.
- Column B contains the Stock.
The steps to detect the smallest value by the SMALL Function in Google Sheets are as follows:
Step 1: Select cell B6 and enter the formula =SMALL(B2:B5,1).
Step 2: Press the “Enter” key. The output is ‘100’, as shown below.
Alternative To The SMALL Function In Google Sheets
An alternative function to the SMALL formula In Google Sheets is the MIN() function.
For example, consider the table below.
The procedure to detect the smallest number using the MIN() and the SMALL() are as follows:
Select cell B5 and enter the formula =min(A2:A4)
For comparison, select cell B6 and enter the formula =small(A2:A4,1)
We see that both the formula has returned the value “1000” as the smallest value.
Important Things To Note
- We will get the #NUM! error
- If the array used is empty.
- If the value of ‘n’ is less than the numerical value is 1 or greater than the values or count in the selected array.
- We will get the #VALUE! error if the value is non-numeric.
- We will get the #NA error if one of the values in the selected array is an error i.e., “#NA” or if one of the argument values is not provided.
Frequently Asked Questions
The SMALL Function in Google Sheets categorized under the Statistical function returns numeric values based on their position in a list, ranked by value, like if the values are sorted in ascending order. It calculates and returns the ‘n’, i.e., the smallest value in a dataset, depending on the nth-value input.
The SMALL function in Google Sheets may not work for the following reasons,
1. The selected dataset is empty or deleted.
2. The value of ‘n’ is less than the numerical value is 1 or greater than the values or count in the selected array.
3. The data value is non-numeric. It must always be a numeric value irrespective of whether it is an integer or a decimal number.
4. One if the argument value is not provided, i.e., either the cell range is not selected or the nth value is not given.
We often forget in which category a function falls, here, the “SMALL” function. Then, we can insert the function as follows:
Choose an empty cell 🡢 select the “Insert” tab 🡢 click the “Function” option right arrow 🡢 click the “All” option right arrow 🡢 select the “SMALL” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the SMALL function by following the path shown below.
• Choose an empty cell 🡢 click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
• A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
• Here, click the “Functions” option 🡢 click the “All” option right arrow 🡢 select the “SMALL” function, as shown below.
Download Template
This article must help understand SMALL Function in Google Sheets with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to SMALL Function In Google Sheets. We use it to find the nth smallest value in a dataset with examples & working template. You can learn more from the following articles.
Leave a Reply