Check Sheet Logo
Check Sheet

SMALL

The SMALL function is a statistical function that returns the nth smallest value in a range or array of data. It is commonly used to find the lowest or highest values in a set of data.

Usage

Use the SMALL formula with the syntax shown below, it has 2 required parameters:

=SMALL(data, n)
Parameters:
  1. data (required):
    The range or array of data to evaluate. Non-numeric values in the data are ignored.
  2. n (required):
    The position of the value to return in the sorted array. Must be greater than 0 and less than or equal to the total number of values in the data.

Examples

Here are a few example use cases that explain how to use the SMALL formula in Google Sheets.

Finding the lowest value in a range

To find the lowest value in a range of data, you can use the formula =SMALL(A1:A10,1), where A1:A10 is the range of data to evaluate and 1 is the position of the smallest value.

Finding the nth smallest value in a range

To find the nth smallest value in a range of data, you can use the formula =SMALL(A1:A10,3), where A1:A10 is the range of data to evaluate and 3 is the position of the third smallest value.

Finding the lowest value in an array

To find the lowest value in an array of data, you can use the formula =SMALL({1,2,3,4,5},1), where {1,2,3,4,5} is the array of data to evaluate and 1 is the position of the smallest value.

Finding the nth smallest value in an array

To find the nth smallest value in an array of data, you can use the formula =SMALL({1,2,3,4,5},3), where {1,2,3,4,5} is the array of data to evaluate and 3 is the position of the third smallest value.

Common Mistakes

SMALL not working? Here are some common mistakes people make when using the SMALL Google Sheets Formula:

Incorrect range specified

One common mistake is specifying an incorrect range for the data argument. Double check that the range includes all the necessary cells and does not include any extraneous cells or rows/columns.

Incorrect value of n specified

Another common mistake is specifying an incorrect value of n for the function. Double check the position of the value you want to return in the range and ensure that the value of n is correctly specified.

Related Formulas

The following functions are similar to SMALL or are often used with it in a formula:

  • LARGE

    The LARGE formula is a statistical function that returns the nth largest value in a range or array. It is often used to find the top or bottom values in a dataset or to determine the nth highest or lowest value. The function can be used in combination with other formulas to perform more complex calculations.

  • AVERAGE

    The AVERAGE function calculates the average (arithmetic mean) of the values passed to it. It is commonly used to find the average of a range of cells containing numerical data.

  • MEDIAN

    The MEDIAN function returns the median (middle) value of a set of numbers. It is commonly used to find the middle value in a range of data points. If the number of data points is even, it returns the average of the two middle values. This function can be useful in statistical analysis and data visualization.

  • MODE

    The MODE formula in Google Sheets returns the most frequently occurring value in a dataset. It is commonly used in statistical analysis to identify the value that occurs most frequently in a set of data. The formula requires at least one input value, but can accept multiple values to include in the analysis.

Learn More

You can learn more about the SMALL Google Sheets function on Google Support.