Contents
Excel SMALL Function
When to Use the Excel SMALL Function in Excel
The SMALL function in Excel comes into play when you need to retrieve the nth smallest value from a set of data. It is particularly useful in scenarios where you are dealing with numerical datasets and want to identify or analyze specific data points based on their magnitude.
What Excel SMALL Function Returns
The Excel SMALL function returns the nth smallest value from a given array or range of cells.
Syntax of Excel SMALL Function
The syntax of the Excel SMALL function is as follows:
=SMALL(array, k)
Input Arguments
Here are the input arguments for the Excel SMALL function:
● array: This is the range or array of data from which you want to extract the nth smallest value. It can be a reference to a range of cells or an array of values.
● k: The position of the value to retrieve. It must be a positive integer. If k is 1, the function returns the smallest value; if k is 2, it returns the second-smallest, and so on.
Extra Notes
● If k is less than 1 or greater than the number of data points in the array, the function will return a #NUM! error.
● If the array contains non-numeric values, the SMALL function will return a #NUM! error.
● The SMALL function only considers numeric values in the specified array. Empty cells, text values, and boolean values (TRUE and FALSE) are disregarded.
● If all values in the array are non-numeric, the SMALL function returns an error (#NUM!).
Examples of the Excel SMALL Function
Here are few examples of using the SMALL function in Excel:
Example 1: Basic Usage
Suppose you have a dataset in cells A1 to A10, and you want to find the smallest value. In cell D1, use the following formula:
This will return the smallest value in the range.
Example 2: Dynamic Use with Cell Reference
Suppose you have the value of ‘k’ in cells D3 to D6, and you want to dynamically change the position. Use the following formula:
let’s break down the formula: =SMALL($B$3:$B$12,D3)
- $B$3:$B$12: This part refers to a specific range of cells in column B, from cell B3 to B12. The dollar signs ($) before the column and row references mean that these references are absolute, which implies that they won’t change when you copy the formula to other cells.
- D3: This is the second argument or parameter for the SMALL function. It represents the position of the smallest value you want to find. In this case, it is in cell D3. If D3 contains the number 1, it will find the smallest value; if it contains 2, it will find the second-smallest, and so on.
Example 3: Summing the Values with SUMPRODUCT and SMALL Functions
Imagine you manage an office supplies store, and you have a dataset containing information about various products and their respective values. Your goal is to sum the lowest 3 values from the array. To achieve this, you use the Excel SUMPRODUCT function in conjunction with the SMALL function.
let’s break down the formula: =SUMPRODUCT(SMALL(B3:B15, {1,2,3}))
- B3:B15: This is the range of values in column B from cell B3 to B15.
- SMALL(B3:B15, {1,2,3}): The SMALL function is finding the 1st, 2nd, and 3rd lowest values in the specified range. The curly braces “{1,2,3}” are used to create an array that tells the SMALL function which positions to consider. It is saying, “Find the 1st, 2nd, and 3rd lowest values”,
- SUMPRODUCT(SMALL(B3:B15, {1,2,3})): The SUMPRODUCT function then takes these three values found by the SMALL function and adds them together.
So, the array “{1,2,3}” serves as a set of positions (1st, 2nd, and 3rd) to be considered by the SMALL function, allowing you to customize which smallest values you want to include in the sum.
Practice Workbook
Download Free Practice WorkbookConclusion
In conclusion, the Excel SMALL function is a powerful tool for extracting specific values based on their position in a dataset. Whether you are working with financial data, analyzing exam results, or managing project deadlines, the Excel SMALL function can help you identify and analyze the relevant data points.
As with any Excel function, it is important to understand the syntax and input requirements. Practice using the Excel SMALL function in different scenarios to enhance your Excel skills and improve your data analysis capabilities.
Frequently Asked Questions (FAQs)
Q1: Can the SMALL function be used to find the largest values in a dataset?
A1: No, the SMALL function is specifically designed to find the smallest values. To find the largest values, you should use the LARGE function.
Q2: What happens if the specified rank (k) is greater than the number of data points in the array?
A2: In such cases, the SMALL function returns a #NUM! error. Make sure that the rank is within the range of available data points.
Q3: Can the SMALL function handle non-numeric values in the array?
A3: No, the SMALL function is designed for numerical data. If the array contains non-numeric values, the function returns a #NUM! error.
Q4: Can I use a cell reference for the ‘k’ argument?
A4: Yes, you can use a cell reference to dynamically change the position of the value to retrieve.
Q5: Are there any restrictions on the type of values in the array?
A5: The array should contain numeric values; otherwise, the function will return the #NUM! error.
Other Related Excel Functions
Unlocking the Power of Excel COUNT Function for Accurate Numeric Data Analysis
Mastering Excel COUNTA Function: Counting Non-Empty Cells Made Easy
Excel COUNTBLANK Function: How to Count Empty Cells in Excel
Join me on Instagram, YouTube and WhatApp Channel for your daily dose of valuable tips and tricks! Catch insightful videos that will enhance your knowledge and skills. Don’t miss out – follow me now!