In Excel, you can use command from the ribbon quickly find the smallest number, or you can use the SMALL function to find the K-th smallest value from a dataset based on its relative standing.
Example 1: What is the 5th smallest income?
= SMALL(C2:C9,5) The 5th smallest income is $73,069.
Example 2: What is the smallest income?
= SMALL(C2:C9,1) The smallest income is $50,712.
Example 3: What is the 20th smallest income?
= SMALL(C2:C9,20) The result returns to #NUM! error.
There are 8 persons in the sample, so the 20th smallest income does not exist, the SMALL function will return #NUM! error. Similarly, when the array is empty, the SMALL function also returns the #NUM! error.
Formula:
= SMALL(Array, K)
Explanations:
– The array is required; the data array or range which you need to determine the K-th smallest value;
– K is required; the position from the smallest in the data range.
Cautions:
– K should be in the range from 1 (smallest) to n (largest), where n is the number of data points. Otherwise, if k ≤ 1 or if k is greater than the number of data points, the SMALL function returns the # NUM? error.
– If the array is empty, the SMALL function returns the #NUM! error.
