## 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 ExcelWorksheet Function In ExcelThe worksheet function in VBA is used when we need to refer to a specific worksheet. When we create a module, the code runs in the currently active sheet of the workbook, but we can use the worksheet function to run the code in a particular worksheet.read more, 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.

##### Table of contents

- SMALL Function in Excel
- SMALL Formula Excel
- Parameters of the SMALL Function in Excel
- How to Use the SMALL Function in Excel?
- Return Value
- Usage Notes
- How to Use SMALL Function in Excel with Examples
- SMALL in Excel Example #1
- SMALL in Excel Example #2
- SMALL in Excel Example #3
- SMALL in Excel Example #4
- SMALL in Excel Example #5
- SMALL in Excel Example #6
- Top Applications of SMALL Function in Excel
- SMALL Function in Excel – Common Problem
- SMALL Function Errors
- Things to Know about the SMALL Function in Excel
- Recommended Articles

### 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.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tuto**rials)

**–>>** **If you want to learn Excel and VBA professionally**, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from **Basic Excel to Advanced Excel, Macros, Power Query, and VBA.**

### How to Use the SMALL Function in Excel?

Below are the steps of using the SMALL function in Excel:

**First, enter the desired SMALL formula in the required cell. You will get a return value on the argument supplied.****You can manually open the spreadsheet’s SMALL formula Excel dialog box and insert the logical values to attain a return value.****Consider the screenshot below to see the SMALL function option under the statistical function menu.****Click on the “SMALL” option. The SMALL formula Excel dialog box will open where you can put the argument values to obtain a return value.**

### 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 FunctionLARGE Excel FunctionLARGE Function returns the nth largest value from a given set of values. It is a built-in function of Microsoft Excel and is categorized as a Statistical Excel Function. read more, 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 ExcelROW Function In ExcelThe row function in Excel is a worksheet function that displays the current row index number of the selected or target cell. The syntax to use this function is as follows: =ROW( Value ).read more
- ISERROR Function in ExcelISERROR Function In ExcelISERROR is a logical function that determines whether or not the cells being referred to have an error. If an error is found, it returns TRUE; if no errors are found, it returns FALSE.read more
- INT Function in ExcelINT Function In ExcelINT or integer function in excel returns the nearest integer of a given number and is used when we have many data sets and each data in a different format.read more

## Leave a Reply