excel small function

Guri Bee

2 min read

·

22-10-2024

1

0

Share

Mastering the SMALL Function in Excel: A Comprehensive Guide

The SMALL function in Excel is a powerful tool for extracting specific values from a dataset, particularly when you need to find the nth smallest value. This function is versatile and can be used in various scenarios, from finding the lowest performing employees to identifying outliers in a data set.

Understanding the SMALL Function

The SMALL function has the following syntax:

SMALL(array, k)

• array: The range of cells containing the values you want to analyze.
• k: The position of the smallest value you want to return. For example, if k = 1, the function returns the smallest value in the array. If k = 2, it returns the second smallest value, and so on.

Illustrative Example:

Imagine a list of sales figures for different salespeople in a company, as shown below:

Let's say you want to find the second smallest sales figure. Using the SMALL function, you would enter the following formula:

=SMALL(B2:B6, 2)

This formula would return the value 1200, which is the second smallest sales figure in the dataset.

Practical Applications of the SMALL Function:

Here are some real-world examples of how the SMALL function can be used:

• Finding the lowest performing employees: You can use the SMALL function to identify employees with the lowest sales figures or performance metrics.
• Identifying outliers in data: The SMALL function can be used to identify values that deviate significantly from the rest of the data.
• Creating a ranked list: You can use the SMALL function to generate a list of values in ascending order.
• Filtering data based on specific values: The SMALL function can be combined with other functions like IF or COUNTIF to filter data based on certain conditions.

Advanced Techniques with the SMALL Function:

• Combined with other functions: The SMALL function can be combined with other Excel functions, such as SUM, AVERAGE, or COUNT, to perform more complex calculations. For instance, you can find the average of the top three values in a dataset using the SMALL function along with AVERAGE.
• Using the SMALL function with arrays: You can use the SMALL function to extract values from arrays, such as those created by other functions like INDEX or MATCH.

Note: The SMALL function does not handle duplicate values. If the array contains duplicate values, the function will return the same value for multiple positions of k.

Attributions:

The examples and descriptions provided in this article are based on information from the following GitHub repositories:

• Excel Formulae
• Excel Functions

This guide provides a comprehensive overview of the SMALL function, its applications, and advanced techniques. By leveraging this powerful tool, you can analyze data more effectively and extract valuable insights from your spreadsheets.