You can use the SMALL function in Google Sheets to find out the nth smallest value within a cell range.
In cases where you need to return the lowest value in a numeric dataset, surely, the MIN function can do the job. But let’s say you want to return the second, third, fourth, etc., smallest value. How can you do that? Well, it’s actually easy with the help of the
Let’s relate it to a simple scenario.
For example, you have a spreadsheet that contains this set of numbers: 2, 8, 4, 15, 10, 13. If you want to display the third lowest value in this dataset, you can use the
SMALL function, specifying the cell range where they are placed in, and rank from the smallest value of the number you want to return, which is 3. This will then give you “8” as the result.
You can use the
SMALL function in a wide range of situations. But of course, you need to learn its syntax first to use it effectively. And that’s what you’re about to find out in this article. So, let’s get started!
The Anatomy of the SMALL Function
SMALL function in Google Sheets is among the simplest to write. Here’s how you should write it:
- = the equal sign is the first character we need to type in to initiate any function or formula in Google Sheets.
- SMALL() this is our
- data is the first required parameter of the
SMALLfunction. It holds the array or cell range of the dataset you want the function to operate on.
- n is the parameter that will hold the rank of the element you want to return. For instance, setting the n parameter to ‘3’ will cause the function to return the third smallest element within the given dataset.
SMALL function only requires two parameters for it to work, as you can see above. So, it should be easy to remember and implement it to your spreadsheet.
A Real Example of Using the SMALL Function
At this point, I’ll show you how the
SMALL function can be utilized in a real setting.
Let’s consider this scenario.
Below is a spreadsheet that contains the coldest temperature readings ever recorded in several states:
If we want to find out the lowest temperature in the given dataset, we can just write a simple formula with the MIN function:
For some reasons, let’s say we also want to display the second, third, and fourth lowest temperature readings. Since the MIN function is not enough to achieve this, we’ll need to use the
SMALL function instead to carry out this task.
As you can see in the example, Google Sheets was able to display the second up to the fourth lowest temperature readings thanks to the
SMALL function. Isn’t that cool?
If you want to have a copy of the example spreadsheet, go ahead and click the link below:
How to Use SMALL Function in Google Sheets
Let’s put the
SMALL function to the test, this time with a simple activity. We’ll use the example dataset in the previous section, so ensure that you already have a copy of the spreadsheet earlier.
- First, open your copy of the spreadsheet. Within it, you should see this dataset:
- Our objective is to supply the remaining fields with their respective values. We will display the second, third, and fourth lowest temperature values in the cells provided using the
- Let’s start by finding out the second lowest temperature. Select cell E3 in your spreadsheet.
- Next, initiate the SMALL function by typing in ‘=SMALL(‘. You should see a tooltip box appear as you type this function.
- This time, we need to specify the values for the parameters of
SMALL. For the data parameter, set cell range B2:B10 as the value. As for the n parameter, since we’re looking for the second lowest temperature, indicate ‘2’ as the value.
- Finalize the function by typing in close parenthesis ‘)’. Afterward, hit the Enter key on your keyboard. Your spreadsheet should update into this:
- Great job! You just used the
SMALLfunction to determine the second lowest temperature in the dataset. Now, find out the values of the remaining fields using the same function. Just change the n parameter accordingly to determine the third and fourth lowest temperatures:
- For the third lowest temperature, let n be ‘3’
- For the fourth lowest temperature, let n be ‘4’
If you’ve typed the function properly, you should see the following changes in your spreadsheet:
Frequently Asked Questions (FAQ)
- Why does my SMALL function return a #NUM! error?
The reason why you get this error is probably that you’re trying to input a value in the n parameter that is less than or outside the range of the selected cell range. Keep in mind that the n parameter can only hold a value that is within the range of your dataset.
- Can I use the SMALL function to return the nth largest element instead?
Although it’s possible, it’s not actually the ideal function to use in this case. If you want to return the nth largest element in a dataset, use the LARGE function in Google Sheets instead.
There you go! That’s all there is to know about the
SMALL function in Google Sheets. This function is just one of the many useful Google Sheets functions out there. Do you want to learn more? Check out our other articles in Google Sheets.
You can also subscribe to our newsletter to be updated with the latest about Google Sheets.