What is the SMALL Function?
The SMALL Function is categorized as a Statistical functionFunctionsThe SMALL Function is categorized as a Statistical function. It will return numeric values based on their position in a list ranked by value. Essentially, it calculates and returns the k smallest value in a dataset. In financial analysis, the SMALL function can be useful in finding the smallest value. It will return numeric values based on their position in a list ranked by value. Essentially, it calculates and returns the k smallest value in a dataset.
In financial analysis, the SMALL function can be useful in finding the smallest value in a given set of values. For example, if all salespersons are given the same target, we can find out which one achieved the sales target in the shortest time for a given year.
Formula
=SMALL(array,k)
The SMALL function uses the following arguments:
- Array (required argument) – It is an array or range of numerical data for which we want to find the k smallest value.
- K (required argument) – It is the smallest value from the given array.
How to use the SMALL Function in Excel?
To understand the uses of the SMALL function, let us consider a few examples:
Example
Suppose we are given the following data about several runners, along with their start and finish times:
We can use the SMALL function to retrieve the nth lowest value from a set of data, that is, the first, second, or third fastest times in a race.
The formula to be used in finding the winner will be:
The formula to be used in finding the first runner-up will be:
The formula to be used in finding the second runner-up will be:
We get the following results:
Things to remember about the SMALL Function
- #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. It 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.
Click here to download the sample Excel file
Additional resources
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling. To learn more, check out these additional resources:
- Excel Formulas for FinanceExcel for FinanceThis Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel. This guide has examples, screenshots and step by step instructions. In the end, download the free Excel template that includes all the finance functions covered in the tutorial
- Advanced Excel Course
- Advanced Excel Formulas and FunctionsAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career
- Excel Shortcuts for Windows and MacExcel Shortcuts PC MacExcel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts