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 SMALL Function in Excel retrieves the “nth smallest” values- like the smallest value, 2nd smallest value, 3rd 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, SMALL Function can also be entered as a part of another formula in a cell of a worksheet.

In terms of financial analysis, SMALL Function is very much 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 SMALL Function, we can find that which person achieved the sales target in the shortest time of the year.

SMALL Formula Excel

Below is the SMALL Formula excel.

SMALL 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?

You can download this SMALL Function Excel Template here – SMALL Function Excel Template

Below are the steps of using small function in excel –

  1. Just enter the desired SMALL in the required cell, and you will get a return value on the argument supplied.

  2. You can manually open the SMALL formula Excel dialogue box in the spreadsheet and enter the logical values to attain a return value.

  3. Consider the screenshot below to see the SMALL function option under the Statistical Function menu.


    SMALL Formula in Excel

  4. Click on the SMALL option. The SMALL formula excel dialogue box will open where you can put the argument values to obtain a return value.


    SMALL Formula in Excel 1

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 a useful function when you are going to retrieve the nth smallest value from the supplied set of data.
  • For example, the SMALL Excel Function can be used to find the first, second, or the third-lowest scores of a test or the fastest times of a race data.
  • Similar to the LARGE Excel Function, the SMALL Function also retrieve numeric values which are based on their position in a supplied list when they are 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 excel and its working.

How to Use SMALL Function in Excel with Examples

Let’s look below at some of the examples of the SMALL function in Excel. These examples will help you in exploring the use of SMALL Formula Excel.

SMALL Function Example

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

SMALL Function Example 1

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 Function Example - 1

SMALL in Excel Example #2

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

SMALL Function Example - 2

SMALL in Excel Example #3

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

SMALL Function Example - 3

SMALL in Excel Example #4

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

SMALL Function Example - 4

SMALL in Excel Example #5

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

SMALL Function Example - 5

SMALL in Excel Example #6

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

SMALL Function Example - 6

Top Applications of SMALL Function in Excel

The Microsoft Excel SMALL Function in Excel can be used 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 given data
  • To get the sum of bottom n values
  • Sorting numbers ascending or descending
  • Extracting multiple matches into several columns
  • Finding 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 which you can face while you are 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. Text values are ignored by the SMALL Function, and it only recognizes numerical values. If you face this error, then convert all the array values into numeric values.

SMALL Function Errors

If you get any kind of error from the SMALL in excel, then it can be any one of the following-

#NUM! – This kind of 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 kind of error occurs when the supplied n is a non-numeric value.

Things to Know about the SMALL Function in Excel

  • The SMALL 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.
  • The function basically returns a numeric value, which is totally based on their position in a supplied list of values when sorted.
  • 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 the SMALL in Excel will be returning #VALUE! error.

Recommended Articles

This has been a guide to SMALL Function in Excel. Here we discuss the SMALL Formula excel and how to use SMALL in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *