How to use Google Sheets SMALL(IF) function. The main goal of the SMALL function is to provide the nth lowest value of the range. Additionally, you can use the SMALL function in a number of other ways when working with Google Sheets.
In this tutorial, Theartcult will show how to use the SMALL function in detail, provide some examples, and show how it can be nested with several other functions.
When use Google Sheets SMALL(IF) function?
The main use of the SMALL function is to return the cell with the lowest value after searching a range of cells. For example, if you have a list of values in column A and want to get the lowest number in the list, you can use the SMALL function to find it.
You can also use the SMALL function to find the 2nd, 3rd, 4th, 5th, etc. lowest value in a range of data. You just need to set the argument n to whatever value you want to find.
Syntax of the SMALL. function
The SMALL function uses two arguments to complete its calculations. Below is an example of the syntactic structure for the SMALL function.
=SMALL(data, n)In there:
- = (equal sign): In Google Sheets, we always start with an equal sign whenever we want to perform a calculation or call a function. It tells the program we want it to treat the text in the cell as a formula.
- SMALL : This is the function name so that Google Sheets knows what calculation to do. In this case, determine the nth smallest value in the data set.
- data : This is the array or range of cells containing the data set that SMALL will look at. Set of tuples containing data that can be in the form of dates, numbers, duration values, etc. Requires all values to be of the same type.
- n : This is an optional argument and represents the rank of the value you want to get, represented as a number. The rating value is a number and must range from 1 to the total number of values present in the set.
For example, if you set n to 4, the SMALL function will find the fourth smallest element in the data set. If you don’t use the n argument , it will search for the smallest value in the range.
How to use the SMALL function in Google Sheets
Check out a few practical examples below to better understand how to use the SMALL function in Google Sheets.
Simple SMALL function
Consider the data set below. Suppose you want to find the lowest value. Here’s how to do it:
1. Click an empty cell, C2 in this case
2. Enter the first part of the formula, which is =SMALL(
3. Highlight or enter the range where you want to find the minimum value. In the example, it’s A2:B11.
4. Enter a comma “,” to indicate that we are moving to the next argument.
5. Enter the argument n . Since you’re looking for the smallest value, enter 1 .
6. Press Enter
Here are the results:
We can continue this pattern but change the value of n to find the 2nd, 3rd lowest, etc…
Sort using SMALL
Sorting data in Google Sheets from smallest to largest is a little known but practical use of the SMALL function. We use the SMALL function and the ROW function to sort the data. Here is the formula for the function you will use:
=SMALL($B$2:$B$11, ROW()-1)Note that the code above locks cell references while copying formulas or using autofill. It does so by using absolute references (the $ sign ) to indicate that those values remain unchanged for formulas in subsequent cells.
This function uses the ROW function to determine the value of n. As a result, it sorts them from smallest to largest, starting with n=1 (lowest number) and up to n=10 (largest number).
Nesting SMALL function with IF
There are other ways to nest SMALL functions, but perhaps the most useful is with the IF function. Let’s say you are interested in finding out the fastest time of a kart driver in class 2 or below from the data above. To do this, we will use the ARRAYFORMULA, SMALL and IF functions, as follows:
=ARRAYFORMULA(SMALL(IF(B2:B9 >=2,C2:C9),1))
The article used Google Sheets ARRAYFORMULA to be able to search two columns at once. And the nested IF function of:
IF(B2:B9>=2,C2:C9)This means that values in column C will only be considered if the value in column B is 2 or more. Check out the IF function tutorial if you want to learn more about how it works.
Potential error cause for SMALL function in Google Sheets
Alphanumeric data
The SMALL function will ignore non-numeric data. Here is an example:
However, columns without numeric data will return #NUM! Error, like this:
This is because the SMALL function cannot handle non-numeric data.
Error out of range
SMALL will generate #NUM! Error when you enter the number n, which is greater than the value of the entries in the data range.
You’ll see an example looking for the 12th smallest number out of a set of only 10 numbers. Therefore, SMALL gives the value #NUM! Error.
Alternative to SMALL function in Google Sheets
The MIN function is an alternative method to find the minimum value in Google Sheets. The MIN function returns the lowest value of the given numbers and is fully automatic.
In this case, the two numbers provided to the MIN and MIN function return a lower value. Its syntax is:
=MIN(value1,value2)
In the above example, the MIN function is used to find the smaller of two numbers. You can also use the MIN function with a range of cells instead of two cell references and it will find the lowest value. However, the MIN function can only find the lowest value, not the nth lowest value.
Now that you understand how the SMALL function works, you’re one step closer to mastering Google Sheets. However, there’s still a lot to learn, so make sure you keep practicing and you’ll definitely turn pro in no time.
Video on How to use Google Sheets SMALL(IF) function
Theartcult hopes that this article has helped you with How to use Google Sheets SMALL(IF) function. If you have any questions or suggestions on this topic, don’t hesitate to leave us a comment below. Thanks for reading!
Visit Macwintips.com to update the most practical and useful tips for Mac and Windows!
