Buzz
While functions like Min and MinA pinpoint the minimum value in a set or range of data, SMALL zeros in on the second, third, or nth smallest numbers in the sequence.
Exploring the Power of SMALL in Excel
Utilizing the SMALL Function in Excel
1. Description and Syntax:
The SMALL function computes and returns the k-th smallest value within the dataset.
Syntax:
Where:
- Array: Represents an array or range of data points from which to determine the K-th smallest value.
- K: The ordinal position (counting from the smallest value) within the data array to return.
Note:
- If Array is empty -> the function returns an error value #NUM!
- If K ≤ 0 or K exceeds the number of data points in Array -> the function returns an error value #NUM!
- With n being the number of data points:
+ SMALL(Array, 1) -> returns the smallest value in Array.
+ SMALL(Array, n) -> returns the largest value in Array.
2. Specific Scenario Examples:
Suppose you need to compute the k-th smallest value according to the description in the dataset table below:
Step 1: Calculate the 2nd smallest value in Array1. In the target cell, enter the formula: =SMALL(B3:B7,2)
Step 2: Press Enter -> the 2nd smallest value in Array1 is:
Step 3: Calculate the 3rd smallest value in Array2. In the target cell, enter the formula: =SMALL(C3:C7,3)
In Array2, there are 2 elements both equal to 39, but the function still sorts them in 2 orders, one being the 3rd smallest value and the other being the 4th smallest value.
Wishing you remember the formulas and usage as shared by Mytour above!
