How to Use the SMALL Function in Google Sheets

This practical guide explains how to use the small function in google sheets with clear steps and useful context. It also covers common requirements, potential problems, and the details that can help you get better results.

When to Use the SMALL Function in Google Sheets?

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 going 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.

Conclusion

Understanding Google Sheets makes it easier to compare options, avoid common mistakes, and apply the information in this guide more effectively. Review the relevant requirements before making changes or choosing a solution.

FAQ

How do you use the small function in google sheets?

Follow the steps in this guide in order, confirm the required settings or tools, and verify the result before making additional changes.

Is it safe to use the small function in google sheets?

It is generally safe when you follow the recommended instructions, use trusted tools, and avoid changing settings you do not understand.

What should you do if the process does not work?

Recheck the requirements, restart the device or application when appropriate, and review each step for missed settings or compatibility issues.