SMALL Function
Returns the ‘kth’ smallest value from the referenced range of data.
The SMALL function can return the 'kth' smallest value from the referenced range of data.
The simplest example that can help to understand how the function works is from the winners of a 100m race.
Suppose 10 participants ran the race, and after the race is over, the committee needs to decide the first three positions based on the least time taken to complete the race.
All the committee needs to do is find the least amount of time from the 10 participants who will be awarded the gold medal. Then, the person with the second least time will be awarded the silver medal, while the third least time will get the bronze medal.
The SMALL function works similarly, where you need to reference a range of values and input the 'kth' value that finds the corresponding 'kth' smallest number from the dataset.
In this article, we will see the SMALL function, how to use the position, and a couple of examples to better understand the process.
SMALL function - What is it?
The SMALL is categorized as a Statistical function that finds the 'kth' smallest value from the dataset.
For example, suppose you have five numbers 10, 12, 14, 16, and 18. Then, if you want to find the third smallest number, the function returns the result as 14.
Similarly, if the number you require is the second smallest, the function will return the result as 12.
The syntax for the function is
=SMALL(array, k)
where,
array - (required) the range of numerical values which will be evaluated for the 'kth' smallest number
k - (required) the 'kth' value that returns the specific smallest number
An important thing you need to remember is the upper limit for the k argument. For example, if there are 15 observations in the referenced data, then the maximum number you can assign for k equals 15.
Any number beyond that will return the #NUM! Error. The numerical value 1 corresponds to the smallest value in the dataset.
If you input a zero or negative integer as an argument, the function still returns the #NUM! Error.
How to use the function?
You may prefer using the function as a worksheet formula rather than from the library. Thus, if you need to input the procedure, you must begin with an equal sign followed by the function name and input those two arguments.
Suppose you have the test scores for the students, as illustrated below:
To get the lowest scoring score on the Geography test, we will use the formula =SMALL(C3:C12,1) in cell F6, which gives the result of 39.
A bit of an investigation shows that this is, in fact, correct, as Ricky Colon scored 39 on his geography test. The number 1 assigned to the k argument means this is the lowest possible value the function can return for us.
If the number is changed to 2, 3, and so on, the test score returned is second smallest, third smallest, etc.
Another thing you can interpret from the result is that the function automatically assumes the ascending order for the data. This way, even if your data is highly disoriented, the process can still find the correct smallest value.
Examples
This section will show examples of using the function in real-life scenarios.
a) Example #1: Based on Date and Time
We know that date and time values are stored as numbers in Excel. The date is stored as serial numbers, whereas time is stored as decimal numbers.
The 24-hour clock will have values between 0 and 1, while the dates begin from 1st January 1900, and the corresponding serial number starts from 1.
Suppose you are a swing trader and make several trades, as illustrated below:
The data looks disoriented. However, we will use the formula =SMALL(B3:B12,1), which gives the serial number for the date as 44781. Format the serial number, which offers the first investment date as 08/08/2022.
Even though the date is somewhere in the middle of the data, the function can still retrieve the first investment date easily by arranging the data in ascending order behind the scenes.
If we had time instead of date, the function still works similarly, i.e., it can find the 'nth' smallest value.
To get the time for the first traded stock, we will use the formula =SMALL(B3:B12,1), which gives the result:
Returning time may need to be clarified for you. Here, you can also use the combination of the INDEX MATCH function that will let you return the Stock name from column C.
The formula will be =INDEX(C3:C12, MATCH(SMALL(B3:B12,1), B3:B12,0)), which gives the stock as Nvidia Inc.
We have nested the SMALL function inside the MATCH, which looks for the smallest value, whereas the INDEX returns the stock name from column C.
Using the INDEX MATCH combination and the SMALL function, we get the result as Nvidia Inc, the first stock traded for the day.
2) Example #2: Sum or Average of the 'n' smallest values
The SMALL function can also be combined with VLOOKUP and AVERAGE to get the average of 'n' most minor numbers.
Let's say that a competition is held as to who solves a Rubik's cube in the fastest time. Each person is allowed to take five retries and will be judged on an average of three, where they take the least amount of time.
The data looks as illustrated below:
Quite impressive that almost all the participants had mostly finished the Rubik's cube in less than 20 seconds.
However, to find what participant used the least amount of average time, we will use the formula =AVERAGE(SMALL(VLOOKUP(D14, B2:G12,{2,3,4,5,6}, FALSE),{1,2,3})) in cell E14, and press the Ctrl + Shift + Enter key, which gives the average time for Ken Sharp as 7.42648 seconds.
This is an array formula where we ask the VLOOKUP function to return the array of values for Ken Sharp. The result for the variety is {3.2969, 13.3490, 10.5045, 8.4781, 13.7613}.
Further, the SMALL function is used to find the three shortest times from the array, which gives us {3.2969, 10.5045, 8.4781}. Finally, the AVERAGE part is used on this array, which returns the result as 7.42648.
If we do not use the Ctrl + Shift + Enter key, the function returns the #N/A Error.
Similarly, you can use the SUM function instead of AVERAGE to get the sum of the 'n' most minor numbers.
LARGE vs. SMALL
LARGE is categorized as a statistical function that 'kth' most significant value from the given dataset. The process works exactly opposite to its counterpart, i.e., returning the most significant values based on the most minor 'k' argument.
Suppose you have the numbers 10, 18, 22, 35, and 48. If you need to find the largest number, the function will return the number as 48. The second largest number will be 35, the third largest will be 22, and so on.
Both functions have a similar syntax, i.e.,
=LARGE(array, k)
where
Array - (required) the range of numerical values which will be evaluated for 'kth' largest number
k - (required) the 'kth' value that returns the specified largest number
Note: The minimum value for the k argument is 1. Any number assigned less than 1 will return a #NUM Error. If the k is greater than the number of observations in the dataset, the function will still return the #NUM! Error.
Note
The minimum value for the k argument is 1. Any number assigned less than 1 will return a #NUM Error. If the k is greater than the number of observations in the dataset, the function will still return the #NUM! Error.
Let's see an example of how contrasting the results returned are from both functions. Suppose you have the day trading data for Tesla Inc, as illustrated below:
We will use the formula =SMALL(D3:D7,1) in cell G4, which gives us the lowest price paid for Tesla Stock, $179.80.
Similarly, to get the highest price, we will use the formula =LARGE(D3:D7,1), giving us $180.89.
We can use both functions alternatively in real life to see what differentiated results we get from the given dataset.
MIN vs. SMALL
MIN gets a special mention due to the similarity in both functions' results.
MIN is another Statistical function that returns the minimum value from a set of numerical values.
Wait… Haven't we heard that before? You are right; even the SMALL function returns the minimum value when your k argument equals 1.
The syntax for the MIN function is:
=MIN(number1, [number2])
where,
number1 - (required) numerical value or the referenced range of cells consisting of numerical values
number2 - (required) second numerical value or the referenced range of cells consisting of numerical values.
If you need to return the minimum value for the dataset, we suggest you use the MIN function since the result returned would be the same as the SMALL. Allow us a chance to prove it.
Suppose you have the day trading data, as illustrated below:
We will use the formula =SMALL(D3:D7,1) in cell G4, giving the $179.80. Similarly, we will use the formula =MIN(D3:D7), which, not surprisingly, also gives the result of $179.80.
Thus, if you intend to get the 'smallest' or the 'lowest' value, you can use the MIN function as an alternative; otherwise, proceed with the SMALL function.
Key Takeaways
- The SMALL function returns the 'kth' smallest value from the given dataset.
- The lower limit for the k argument is equal to 1. If you input any number less than that, the function returns the #NUM! Error. Beyond that number, the function returns the #NUM! Error.
- If the referenced range is empty, the function returns the #NUM! Error.
- You will get a value error if a non-numeric value is an input for the k argument.
- The LARGE function returns the 'kth' largest value from the referenced range of cells.
- Similar to the SMALL function, the upper and lower limit for the LARGE function is 1 and the total number of observations in the dataset, respectively.
- The smallest value returned by the SMALL function and the result returned by the MIN function is the same. Therefore, you can use the latter function as an alternative to finding the smallest value from the dataset.
or Want to Sign up with your social account?