The SMALL function returns the nth-smallest value within a set of numeric values. The smallest value is ranked number 1.
SMALL(value-set, ranking)
value-set: A collection of number values, date/time values, or duration values. All values must be of the same value type.
ranking: A number value representing the size ranking of the value you want to retrieve. ranking must be in the range of 1 to the number of values in the collection.
Notes
A ranking of 1 retrieves the smallest number in the collection, 2 the second-smallest, and so on. Values included in the collection that are of the same size are ranked together, but impact the outcome.
Examples |
|---|
Suppose the following table contains the cumulative test scores for this semester for your 20 students. (The data is organized this way for the example; it would likely originally have been in 20 separate rows.) |
A | B | C | D | E | |
|---|---|---|---|---|---|
1 | 30 | 75 | 92 | 86 | 51 |
2 | 83 | 100 | 92 | 68 | 70 |
3 | 77 | 91 | 86 | 85 | 83 |
4 | 77 | 90 | 83 | 75 | 80 |
=SMALL(A1:E4, 1) returns 30, the smallest cumulative test score (cell A1). =SMALL(A1:E4, 2) returns 51, the second-smallest cumulative test score (cell E1). =SMALL(A1:E4, 6) returns 75, the sixth-smallest cumulative test score (order is 30, 51, 68, 70, 75, then 75 again, so 75 is both the fifth- and sixth-smallest cumulative test score). |