SMALL

In this comprehensive guide, we will explore the SMALL function in Excel, which is a powerful tool for extracting the smallest values from a dataset. We will cover the syntax, examples, tips and tricks, common mistakes, troubleshooting, and related formulae. By the end of this article, you will have a deep understanding of the SMALL function and be able to use it effectively in your Excel spreadsheets.

SMALL Syntax

The SMALL function in Excel is used to find the nth smallest value in a dataset. The syntax for the SMALL function is as follows:

=SMALL(array, k)

Where:

• array is the range of cells containing the dataset from which you want to extract the smallest value(s).
• k is the position of the smallest value you want to find. For example, if you want to find the 3rd smallest value, you would use k=3.

SMALL Examples

Let’s look at some examples of how to use the SMALL function in Excel.

Example 1: Finding the smallest value in a dataset

Suppose you have a dataset of test scores in cells A1:A10 and you want to find the smallest score. You can use the SMALL function as follows:

=SMALL(A1:A10, 1)

This formula will return the smallest value in the range A1:A10.

Example 2: Finding the 3rd smallest value in a dataset

Using the same dataset of test scores in cells A1:A10, you can find the 3rd smallest score by using the following formula:

=SMALL(A1:A10, 3)

This formula will return the 3rd smallest value in the range A1:A10.

Example 3: Finding the smallest value in a dataset with multiple criteria

Suppose you have a dataset of test scores in cells A1:A10 and you want to find the smallest score for students who scored above 50. You can use the SMALL function in combination with the IF function as follows:

=SMALL(IF(A1:A10>50, A1:A10), 1)

This formula will return the smallest value in the range A1:A10 that is greater than 50.

SMALL Tips & Tricks

Here are some tips and tricks to help you get the most out of the SMALL function in Excel:

1. Use the SMALL function with other functions like IF, AND, or OR to apply multiple criteria when finding the smallest value.
2. Combine the SMALL function with the ROW or COLUMN function to find the row or column number of the smallest value in a dataset.
3. Use the SMALL function with the INDEX function to return the smallest value along with its corresponding data in another column or row.
4. Remember that the SMALL function ignores empty cells, text, and logical values in the dataset. If you need to include these values, use an appropriate function like IF or IFERROR to convert them to numerical values.

Common Mistakes When Using SMALL

Here are some common mistakes to avoid when using the SMALL function in Excel:

1. Using a non-numeric value for the k argument. The k argument should always be a positive integer.
2. Using an incorrect range for the array argument. Make sure to select the correct range of cells containing the dataset from which you want to extract the smallest value(s).
3. Forgetting to use an array formula when combining the SMALL function with other functions like IF, AND, or OR. To enter an array formula, press Ctrl+Shift+Enter instead of just Enter after typing the formula.

Why Isn’t My SMALL Function Working?

If your SMALL function isn’t working as expected, consider the following troubleshooting steps:

1. Check the syntax of your formula to ensure you have entered the correct arguments for the SMALL function.
2. Make sure you have entered the correct range for the array argument and the correct value for the k argument.
3. Ensure that your dataset contains numeric values. The SMALL function will ignore empty cells, text, and logical values.
4. If you are using the SMALL function with other functions like IF, AND, or OR, make sure to enter the formula as an array formula by pressing Ctrl+Shift+Enter instead of just Enter.

SMALL: Related Formulae

Here are some related formulae that you may find useful when working with the SMALL function in Excel:

1. LARGE: This function is used to find the nth largest value in a dataset. The syntax is similar to the SMALL function: =LARGE(array, k).
2. MIN: This function returns the smallest value in a dataset. The syntax is =MIN(number1, [number2], …).
3. MAX: This function returns the largest value in a dataset. The syntax is =MAX(number1, [number2], …).
4. RANK: This function returns the rank of a specific value in a dataset. The syntax is =RANK(number, ref, [order]).
5. PERCENTILE: This function returns the value at a specified percentile in a dataset. The syntax is =PERCENTILE(array, k).

By understanding the SMALL function and its related formulae, you can effectively analyze and manipulate data in Excel. With this comprehensive guide, you should now be well-equipped to use the SMALL function in your spreadsheets and avoid common pitfalls.