SMALL Function in Excel

SMALL Function in Excel

The Microsoft Excel SMALL function is the function that is responsible for returning the nth smallest value from the supplied set of values. In other words, we can say that the SMALL function in Excel retrieves the "nth smallest" values -like the smallest value, second smallest value, third smallest value, etc. The SMALL function in Excel is a built-in function of Excel and is categorized as a Statistical Function in excel. As a part of the worksheet function in Excel, the SMALL function can also be entered as a part of another formula in a worksheet cell.

In financial analysis, the SMALL function is useful for finding the smallest value in a supplied set of values in Microsoft Excel. For example, if the entire salesman in a company is given the same target, then with the help of the SMALL function, we can find which person achieved the sales target in the shortest time of the year.

SMALL Formula Excel

Below is a SMALL function Excel formula.

Parameters of the SMALL Function in Excel

The SMALL function accepts the following parameters and arguments:

array – This is the range or array from which you want the function to return the nth smallest value.

nth_position – This is an integer that specifies the position from the value, which is the smallest, i.e., the nth position.

How to Use the SMALL Function in Excel?

Return Value

The return value will be a numeric value, which is the nth smallest value in the array. Please note that if the nth_position is a larger value than the number of values in the array, then the SMALL function will return #NUM! Error. If the supplied array is empty, then the SMALL function will be returning #NUM! Error.

Usage Notes

• The SMALL function is useful when you retrieve the nth smallest value from the supplied data set.
• For example, we can use the SMALL Excel function to find the first, second, or third-lowest test scores or the fastest times of a race data.
• Similar to the LARGE Excel Function, the SMALL function retrieves numeric values based on their position in a supplied list when sorted by value.
• Please note that Microsoft Excel uses "k" instead of "n." We are using "nth" because it is much easier to understand the SMALL formula and its works.

How to Use SMALL Function in Excel with Examples

Let us look below at some examples of the SMALL function in Excel. These examples will help you explore using the SMALL formula in Excel.

Based on the above SMALL, an Excel spreadsheet, let us consider these examples and see the SMALL function return based on the function's syntax.

Consider the below screenshots of the above SMALL in Excel examples for clear understanding.

SMALL in Excel Example #1

Using the SMALL formula in Excel =SMALL(A1:A5, 1), we get -3.5.

SMALL in Excel Example #2

Apply the SMALL formula =SMALL(A1:A5, 2) to get 4.

SMALL in Excel Example #3

Applying the SMALL in Excel here =SMALL(A1:A5, 3) to get 6.8.

SMALL in Excel Example #4

Apply the SMALL formula here =SMALL(A1:A5, 4) to get 7.

SMALL in Excel Example #5

Here, we apply the SMALL formula in Excel =SMALL(A1:A5, 5) to get 36.

SMALL in Excel Example #6

Here we use the formula to calculate the SMALL in Excel =SMALL({6, 23, 5, 2.3}, 2)

Top Applications of SMALL Function in Excel

We can use the Microsoft Excel SMALL function in Excel for various purposes and applications within the spreadsheet. Some of the common applications of SMALL Excel function spreadsheets are given below:

• Highlighting the bottom values of the provided data
• To get the sum of the bottom n values
• Sorting numbers ascending or descending
• Extracting multiple matches into several columns
• Finding the lowest n values
• Extracting multiple matches into several rows
• Sum bottom n values with criteria

SMALL Function in Excel - Common Problem

One of the most common problems you can face while using the SMALL function is that it returns the wrong value, or the return value is nothing but an error such as #NUM! Error. This error can occur even if the supplied value of k is between 1 and the supplied array value numbers. Possibly, this can arise when you have included text representations of the number within the supplied array. The SMALL function ignores text values and only recognizes numerical values. If you face this error, convert all the array values into numeric ones.

SMALL Function Errors

If you get any error from the SMALL in Excel, then it can be any one of the following:

#NUM! – This error occurs when the supplied value of n is less than the numerical value 1 or greater than the values in the supplied array. Apart from this, this error can also occur if the supplied array is empty.

#VALUE! – This error occurs when the supplied n is a non-numeric value.

Things to Know about the SMALL Function in Excel

• The SMALL function in Excel is the function that is responsible for returning the nth smallest value from a given set of values in the spreadsheet.
• The SMALL in Excel is categorized as a Statistical Function.
• When sorted, the function returns a numeric value based on its position in a supplied list of values.
• If the supplied array is empty, then the SMALL in Excel will be returning #NUM! Error.
• If the supplied value of n is less than the numerical value 1 or greater than the values in the supplied array, then the SMALL in Excel will be returning #NUM! Error.
• If the supplied value is non-numeric, then Excel's SMALL function will return #VALUE! Error.

Recommended Articles

This article has been a guide to SMALL Function in Excel. Here, we discuss the SMALL formula in Excel and how to use SMALL in Excel, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: -

• FIND Excel Function
• ROW Function in Excel
• ISERROR Function in Excel
• INT Function in Excel