The Excel SMALL function is a Statistical formula that sorts numbers in a list from smallest to largest and then gives you the number that is in a certain position. In short, it returns the kth smallest value in a list. In this guide, we’re going to show you how to use the Excel SMALL function and also go over some tips and error handling methods.
Supported Versions
- All Excel versions
Excel SMALL Function Syntax
The syntax of the SMALL function in Excel is simple and consists of two variables as shown below:
array: This is the range of numbers or cells containing numerical values from which you want to find the k-th smallest value. The array can be a set of individual numbers, cell references, or a combination of both.
k: This is an integer that specifies the position from the smallest value within the array that you want to return. For instance, if you set k to 1, the function will return the smallest number in the array. If you set k to 3, it will return the third smallest number, and so on.
It's important to note that the k parameter must be a positive integer, and if k is greater than the number of values in the array, the SMALL function will return an error. This function is commonly used in data analysis for tasks such as finding the minimum, quartiles, or other statistical measures within a dataset.
Examples of SMALL Function
Find the Smallest k-th Number
This formula is an excellent example of how the SMALL function can be utilized to identify the smallest numbers within a specified range. In this case, the formula is set up to return the 5th smallest number from the selected range. For instance, if the range contains the numbers {20; 40; 40; 60; 78; ...}, the formula will yield the value 78, which is the 5th smallest in this sequence.
An interesting aspect of the SMALL function is how it handles recurring values within a range. Each occurrence of a number is counted separately by the function. Therefore, in the given example, even though 78 is technically the 4th largest number if we were to sort the range, the SMALL function considers both instances of the number 40. This means that each 40 is counted individually when determining the position of 78 as the 5th smallest number.
Excel SMALL Formula for Dates and Times
Excel has a unique way of handling dates and times, treating them as numbers behind the scenes. This approach allows for a wide range of calculations and manipulations of date and time data, similar to how numerical data is handled. In Excel, each date is represented as a sequential serial number where 1 represents January 1, 1900. Time values are represented as fractional parts of a day; for instance, 0.5 corresponds to 12:00 PM. This numerical representation of dates and times opens up possibilities for using statistical functions like the SMALL function with date and time data.
Given this understanding of how Excel processes dates and times, the SMALL function can be effectively applied to find the k-th smallest date or time in a given set. In this data sample of release dates, using the SMALL function with a specified k value can help identify the k-th earliest date. Similarly, for time data such as timestamps in a log, the SMALL function can pinpoint the k-th earliest time recorded. This capability is particularly useful in scenarios where sorting and identifying key dates or times is essential, such as scheduling, timeline management, and historical data analysis. By treating dates and times as integers, Excel's SMALL function becomes a versatile tool in both numerical and chronological data analysis.
Use of Excel SMALL Function versus MIN Function
The Excel SMALL function, while unique in its specific application, shares similarities and contrasts with several other Excel functions. A direct comparison can be made with the MIN function, which is also used to find the smallest value in a range. The MIN function is straightforward; it simply returns the smallest value in the given range. For example, if you have a dataset with the values {10, 20, 30, 40, 50}, using =MIN(A1:A5) (assuming these values are in cells A1 through A5) will return 10, the smallest number in this range.
The SMALL function, on the other hand, offers more nuanced control by allowing you to specify which smallest value you want to retrieve. This is particularly useful in scenarios where you need to identify not just the minimum value, but perhaps the second or third smallest value in a range. Continuing with the previous example, if you use =SMALL(A1:A5, 2), the function will return 20, which is the second smallest value in the range. If you change the formula to =SMALL(A1:A5, 3), it will return 30, the third smallest value, and so on.
This distinction makes the SMALL function more versatile in many situations. For instance, in a classroom setting, while MIN can be used to find the lowest score in a test, SMALL can help in ranking students' performances, like finding the second or third lowest score. Therefore, while MIN is ideal for quickly finding the minimum value, SMALL is more suited for detailed analysis where the position of a value within a range is significant.
Error Handling in SMALL Function
There are a few common errors that users may encounter while working with this function, and understanding how to address them is crucial.
#NUM!: The most frequent error associated with the SMALL function is the #NUM! error. This occurs when the 'k' value specified in the function is either less than 1 or greater than the number of elements in the array. It also occurs when the array is empty. For example, if your array has 10 elements and you set 'k' to 11 (or 0), the SMALL function will return a #NUM! error. To prevent this, always ensure that the 'k' value is within the range of the number of elements in your array. Additionally, double-check your array to ensure it doesn't contain non-numeric values, as SMALL function is designed to work with numerical datasets only.
#VALUE!: Another common error is the #VALUE! error, which appears when the function contains inappropriate arguments or if the array is empty. This can happen if non-numeric values are included in the array or if the array reference is incorrect. To handle this, verify that your array consists solely of numeric values and that the range specified is accurate. Also, ensure that the array is not left blank.
A simple approach to manage potential errors in the SMALL function is to use it in conjunction with the IFERROR or IF functions. IFERROR can be used to provide an alternative result or a custom message when an error is detected. For instance, =IFERROR(SMALL(A1:A10, 2), "Error in data") will return "Error in data" if there is an error in the SMALL function. Similarly, the IF function can be used to check the 'k' value before applying the SMALL function, like =IF(K<=COUNT(A1:A10), SMALL(A1:A10, K), "k is too large"). This formula will check if 'k' is within the acceptable range, and if not, it will return a custom error message.
Summary and Tips
- Use the Excel SMALL function to determine the k-th smallest value in an array or range.
- The SMALL function can work with arrays/ranges, including those that contain date/time values.
- =SMALL(array,1) returns the smallest value. The MIN function works the same way.
- Use the LARGE function to get k-th largest value from an array.
- The SMALL function can be used with other functions for sorting. See How to filter by using a formula in Excel
