SMALL
The SMALL function in Excel is used to extract the nth smallest value from a set of values. It is handy when you need to find the smallest values in a range or list.
Syntax
=SMALL(array, k) Arguments
| Argument | Required | Description |
|---|---|---|
| array | Yes | The array or range of values from which you want to extract the nth smallest value. |
| k | Yes | The position of the value that you want. For example, k=1 to get the smallest value, k=2 for the second smallest value, and so on. |
About
When you're faced with a dataset and the need arises to pinpoint specific lower values within it, the SMALL function serves as an indispensable tool in the realm of Excel. This function thrives on efficiency, allowing you to effortlessly extract the nth smallest value from a given array or range. This capability proves invaluable in scenarios where you're analyzing data sets and seeking the smallest elements within them for further assessment or comparison purposes. Whether you're dealing with financial figures, statistical data, or any set of values, SMALL steps in to simplify the task of isolating and working with the smaller components of your dataset.
Examples
Suppose you have a list of numbers in cells A1:A5 (5, 3, 8, 2, 1) and you want to find the 2nd smallest number from that list. The SMALL formula would be: =SMALL(A1:A5, 2). This will return the value 2 as the 2nd smallest number from the list.
Consider a scenario where you have a dataset of sales figures in cells B1:B10 and you need to determine the 3rd lowest sales value from that dataset. The formula to achieve this would be: =SMALL(B1:B10, 3). Upon execution, this formula will provide you with the 3rd smallest sales figure from the dataset.
Consider a scenario where you have a dataset of sales figures in cells B1:B10 and you need to determine the 3rd lowest sales value from that dataset. The formula to achieve this would be: =SMALL(B1:B10, 3). Upon execution, this formula will provide you with the 3rd smallest sales figure from the dataset.
Tips & notes
The SMALL function operates based on the specified position (k) within the array, enabling you to flexibly extract the desired smaller values from your data. Ensure that the array you provide to the function does not contain any non-numeric values, as this may lead to unexpected results or errors.
Common questions
How does the SMALL function differ from the MIN function in Excel?
While the MIN function returns the smallest value from a range of cells, the SMALL function allows you to specify the position of the value you want to extract. This flexibility provided by the SMALL function is particularly useful when working with datasets where you need the nth smallest value, rather than just the overall smallest value.
Can the SMALL function be used to find the nth largest value in a dataset?
Yes, by adjusting the value of k in the SMALL function, you can find the nth largest value in a dataset. For example, setting k=1 would return the largest value, k=2 would return the second largest value, and so on.
Does the SMALL function work with arrays containing non-numeric values?
No, the SMALL function is designed to work with arrays or ranges of numerical values. If non-numeric values are present in the array, the SMALL function may result in errors or unexpected outcomes.