How to Use the SMALL Function in Google Sheets

The main purpose of the SMALL function is to provide the nth smallest value within a range. Additionally, there are several other ways you can use the SMALL function when working with Google Sheets.

In this guide, Officehabit will discuss the SMALL function in detail, provide some examples, and demonstrate how it can be combined with other functions.

When to Use the SMALL Function in Google Sheets?

The main purpose of the SMALL function is to return the smallest value after searching within a range of cells. For example, if you have a list of values in column A and want to retrieve the lowest number in the list, you can use the SMALL function to do so.

You can also use the SMALL function to find the second, third, fourth, fifth, etc. smallest values within a range of data. Simply set the argument ‘n’ to any value you want to find.

Syntax of the SMALL Function

The SMALL function uses two arguments to perform its calculations. Here is an example of the syntax structure for the SMALL function.

=SMALL(data, n)

In which:

= (equals sign): In Google Sheets, we always start with an equals sign whenever we want to perform a calculation or call a function. It lets the program know that we want it to interpret the text in the cell as a formula.

SMALL: This is the name of the function that Google Sheets recognizes to perform a specific calculation. In this case, it identifies the nth smallest value in a dataset.

data: This is the array or range of cells that contains the dataset that SMALL will consider. The set of values can be in the form of dates, numbers, durations, etc. It is important that all values are of the same type.

n: This is an optional argument and represents the rank of the value you want to retrieve, expressed as a number. The rank value is a number and must be within the range from 1 to the total number of values in the set.

For example, if you set n to 4, the SMALL function will find the fourth smallest element in the dataset. If you don’t use the n argument, it will search for the smallest value within the range.

How to Use the SMALL Function in Google Sheets

Let’s explore some practical examples below to better understand how to use the SMALL function in Google Sheets.

Simple SMALL function

Consider the dataset below. Suppose you want to find the smallest value. Here’s how to do it:

  1. Click on an empty cell, C2 in this case.
  2. Enter the first part of the formula, which is =SMALL(
  3. Select or enter the range in which you want to find the smallest value. In the example, it is A2:B11.
  4. Enter a comma “,” to indicate that we are moving to the next argument.
  5. Enter the argument for ‘n’. Since you are looking for the smallest value, enter 1.
  6. Press Enter.

Here is the result:

We can continue this pattern but change the value of ‘n’ to find the second, third, etc. smallest values.

Sorting Using SMALL

Sorting data in Google Sheets from smallest to largest is a practical but lesser-known use of the SMALL function. We can use the SMALL function along with the ROW function to sort the data. Here is the formula for the function that you will use:

=SMALL($B$2:$B$11, ROW()-1)

Note that the code above locks the cell references when copying the formula or using the autofill feature. It does so by using absolute references (the $ sign) to indicate that those values should not change for the formulas in the 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 (the smallest number) and up to n=10 (the largest number).

Nested SMALL function with IF

There are various ways to nest the SMALL function, but perhaps the most useful one is with the IF function. Let’s say you are interested in finding the fastest lap time of kart drivers in the second or lower ranks from the given data. 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 utilized Google Sheets’ ARRAYFORMULA to search for two columns simultaneously. And the nested IF function:

IF(B2:B9>=2,C2:C9)

This means that the values in column C will only be considered if the values in column B are 2 or greater. Please refer to the IF function documentation if you want to learn more about how this function works.

Underlying Causes of Errors for SMALL Function in Google Sheets

Text and Numeric Data

The SMALL function will ignore non-numeric data. Here is an example:

However, columns without numeric data will return a #NUM! error, like this:

This is because the SMALL function cannot handle non-numeric data.

Out of Range Error

SMALL will generate a #NUM! error when you input a value for ‘n’ that is greater than the number of entries within the data range.

You will see an example attempting to find the 12th smallest number in a set that only consists of 10 numbers. Therefore, the SMALL function returns a #NUM! error.

Alternative to the SMALL function in Google Sheets

The MIN function is an alternative method to find the smallest value in Google Sheets. The MIN function returns the lowest value among the given numbers and is entirely automatic.

In this case, two numbers are provided to the MIN function, and it returns the smaller value. Its syntax is:

=MIN(value1,value2)

In the above example, the MIN function is used to find the smaller number among the two numbers. You can also use the MIN function with a range of cells instead of two cell references, and it will find the minimum value. However, the MIN function can only find the smallest value, not the nth smallest value.

Now that you understand how the SMALL function works, you have taken a step further on mastering Google Sheets. However, there is still much to learn, so make sure to continue practicing, and you will become proficient in no time.

Leave a Comment

Your email address will not be published. Required fields are marked *

Shopping Basket
Scroll to Top