Small function in excel is used for getting the smallest number from the selected range of numbers with the help of Kth position located in the range. For example, we have 10 different numbers and we need to find the smallest number out of that, by using Small function, we can get the 1st or 2nd or any Kth smallest number out of those 10 selected numbers.
Syntax:= SMALL(array, k)
The SMALL function syntax has the following arguments:
- Array Required. An array or range of numerical data for which you want to determine the k-th smallest value.
-
K Required. The position (from the smallest) in the array or range of data to return.
Example: Let’s look at some Excel SMALL function examples and explore how to use the SMALL function as a worksheet function in Microsoft Excel:
Example 1:
Below are the scores of the students in a test. From the below-given data find the smallest score and the 3rd smallest score.
If we are finding the smallest number, we can simply apply MIN Function. If you look at the below image both the formulas returning the same value as the smallest number in the given list.
Syntax: =MIN(B2:B17)
Result: 5
Syntax: =SMALL(B2:B17,1)
Result: 5
However, MIN Function stops there only it cannot find the 2nd, 3rd and 4th smallest number. In such cases, SMALL can give us the Kth position number.
Find the 4th Smallest Number
To find the third smallest score or number from the list we need to specify the number in the Kth argument.
The formula is =SMALL(B2:B17,4) and it reads like this:
“In the range B2:B17 find the 4th largest value”
So the Result will be: 6.5
Example 2:
Below is the data for a cycle race. From this list, you need to find the winner. Data includes names, start time, end time.
From this list, we need to find who has taken the least time to complete the race.
Step 1: Find the total time taken.
Actual time taken to complete the race arrived by deducting the start time by end time. Below image shows the actual time taken by each one to complete the race.
Step 2: Now apply the SMALL Function to get the winner.
Syntax: =SMALL(D2:D17,1)
Result: 4:22:00
It is a bit of a herculean task if the list is long. But we can just name the winner using if condition.
Based on the Excel spreadsheet above, the following SMALL examples would return:
Syntax: =IF($B$19=D3,”Winner”,”Better luck next time”)
Result: Winner
Syntax: =IF($B$19=D4,”Winner”,”Better luck next time”)
Result: Better luck next time
Syntax: =IF($B$19=D5,”Winner”,”Better luck next time”)
Result: Better luck next time
Syntax: =IF($B$19=D6,”Winner”,”Better luck next time”)
Result: Better luck next time
Syntax: =IF($B$19=D7,”Winner”,”Better luck next time”)
Result: Better luck next time
Syntax: =IF($B$19=D8,”Winner”,”Better luck next time”)
Result: Better luck next time
Syntax: =IF($B$19=D9,”Winner”,”Better luck next time”)
Result: Better luck next time
Syntax: =IF($B$19=D10,”Winner”,”Better luck next time”)
Result: Better luck next time
Syntax: =IF($B$19=D11,”Winner”,”Better luck next time”)
Result: Better luck next time
Syntax: =IF($B$19=D12,”Winner”,”Better luck next time”)
Result: Better luck next time
Syntax: =IF($B$19=D13,”Winner”,”Better luck next time”)
Result: Better luck next time
Syntax: =IF($B$19=D14,”Winner”,”Better luck next time”)
Result: Better luck next time
Syntax: =IF($B$19=D15,”Winner”,”Better luck next time”)
Result: Better luck next time
Syntax: =IF($B$19=D16,”Winner”,”Better luck next time”)
Result: Better luck next time
Syntax: =IF($B$19=D17,”Winner”,”Better luck next time”)
Result: Better luck next time
If the value arrived, by a SMALL function is equal to the actual time took value then we call it as Winner or else Better Luck Next Time.
Note:
- #NUM! error – Occurs when:
- The given array is empty; or
- The given value of k is less than 1 or greater than the number of values in the given array.
- #VALUE! error – Occurs if the given k is non-numeric.
- If n is the number of data points in an array, SMALL(array,1) equals the smallest value, and SMALL(array,n) equals the largest value.
- Sometimes, the SMALL function will return a wrong value, or will return a #NUM! error, even though the supplied value of k is between 1 and the number of values in the supplied array. This will occur when we provide text values or text representations of numbers within the supplied array. The function ignores text values. Therefore, this problem may arise if the values in the supplied array are text representations of numbers, instead of actual values. We can solve this issue by converting all array values into numeric values.