This article describes the formula syntax and usage of the SMALL function in Microsoft Excel.
Description
Returns the kth smallest value in a data set. Use this function to return values with a particular relative standing in a data set.
Syntax
SMALL(array, k)
The SMALL function syntax has the following arguments:
Array Required. An array or range of numerical data for which you want to determine the kth smallest value.
K Required. The position (from the smallest) in the array or range of data to return.
Remarks
If array is empty, SMALL returns the #NUM! error value.
If k ≤ 0 or if k exceeds the number of data points, SMALL returns the #NUM! error value.
If n is the number of data points in array, SMALL(array,1) equals the smallest value, and SMALL(array,n) equals the largest value.
Example
Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
Data 1  Data 2  

3  1  
4  4  
5  8  
2  3  
3  7  
4  12  
6  54  
4  8  
7  23  
Formula  Description (Result)  Result 
=SMALL(A2:A10,4)  4th smallest number in first column (4)  4 
=SMALL(B2:B10,2)  2nd smallest number in the second column (3)  3 
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, 2^{nd} smallest value, 3^{rd} smallest value, etc. The SMALL Function in Excel is a builtin function of Excel and is categorized as a Statistical Function in excel. As a part of the worksheet 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.
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 –
 Just enter the desired SMALL in the required cell, and you will get a return value on the argument supplied.
 You can manually open the SMALL formula Excel dialogue box in the spreadsheet and enter 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 dialogue 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 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 thirdlowest 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.
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.
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
Now apply 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
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 nonnumeric 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 nonnumeric, 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
SMALL Excel Function
SMALL Function in Excel (Table of Contents)
SMALL Function in Excel
A small function in excel is used for getting the smallest number from the selected range of numbers with the help of the Kth position located in the range. For example, we have 10 different numbers, and we need to find the smallest number out of that; by using the Small function, we can get the 1^{st} or 2^{nd} or any Kth smallest number out of those 10 selected numbers.
SMALL Formula in Excel:
Below is the SMALL Formula in Excel :
The SMALL function has two arguments, i.e. array, k. Both are required arguments.
 Array: This is the range of cells you are selecting it as the source data to find the K value.
 K: This the K^{th }position of the number. From the list, it gives the bottom value.
In this function, the range should not be empty, and we need to specify both the arguments.
How to Use SMALL Function in Excel?
SMALL Function in Excel is very simple and easy to use. Let us now see how to use this SMALL Function in Excel with the help of some examples.
Example #1
Below are the scores of the students on a test. From the belowgiven data, find the smallest score and the 3^{rd} smallest score.
If we are finding the smallest number, we can simply apply MIN Function. If you look at the below image, both the formulas returning the same value as the smallest number in the given list.
However, MIN Function stops there only it cannot find the 2^{nd}, 3^{rd} and 4^{th} smallest number. In such cases, SMALL can give us the Kth position number.
Find the 3^{rd} Smallest Number.
To find the third smallest score or number from the list, we need to specify the number in the Kth argument.
The formula is =SMALL (B2:B11, 3), and it reads like this:
“In the range, B2:B11 find the 3^{rd} largest value.”
So the result will be :
Example #2
Below is the data for a cycle race. From this list, you need to find the winner. Data includes names, start time, end time.
From this list, we need to find who has taken the least time to complete the race.
Step 1: Find the total time taken.
The actual time taken to complete the race arrived by deducting the start time by the end time. The below image shows the actual time taken by each one to complete the race.
Step 2: Now apply the SMALL Function to get the winner.
So the result will be :
It is a bit of a herculean task if the list is long. But we can just name the winner using the if condition.
So the result will be :
Similarly, it is applied to other cells in that column to get the desired output.
If the value arrived by a SMALL function is equal to the actual time taken value, we call it as Winner or Better Luck Next Time.
Example #3
We can use the SMALL Function along with other functions. From the belowgiven list, find the sum of the bottom 3 values for Week2.
Apply the below SMALL function along with the SUM & VLOOKUP function.
This is an array formula. You need to close the formula by typing Ctrl + Shift + Enter. This would insert the curly brackets before and after the formula.
VLOOKUP returns the value for WEEK 2 specified by the SMALL function for 3 bottom values. Then SUM function will add the bottom values together and return the result as 1988.
Things to Remember
 SMALL Function ignores text values and considers only numerical values.
Result is :
 A SMALL function returns an error if there are no numerical values in the list.
Result is :
 If there are any duplicates, then SMALL considers the first value as the smaller one.
 K should be numeric; otherwise, it returns the error as #VALUE!
 Supplied range should not be empty.
 In case of finding only the least value, then we can use the MIN Function. But it finds only the first smallest value.
 Even though SMALL ignores text values, if there are any errors, then it will return the result as #DIV/0!.
 We can use the SMALL along with many other functions to find the Nth values.
 Use practically to get the hint of SMALL function.
 If you are using SMALL with other function, then it becomes an array formula.
Recommended Articles
This has been a guide to SMALL Function in Excel. Here we discuss the SMALL Formula in Excel and how to use a SMALL Function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
 VLOOKUP Function in Excel
 SUM Function in Excel
 SUBTOTAL Function in Excel
 TRUE Function in Excel
Sort numbers using Excel SMALL function
In this article, we will learn about how to Sort numbers using Excel SMALL function with ROWS function in Excel.
Sort numbers in excel using the excel functions. Excel SMALL function returns the kth smallest value from the data. We can get the data in sorted way.
The SMALL function returns the kth smallest value starting from the smallest in a range of values.
Syntax:
=SMALL (array, k)
K : k=1 return the smallest value and if k equals the number of values in an array then it returns the largest value of the data or else it returns #NUM! error. The ROWS function returns the number of rows in excel.
Syntax:
=ROWS (array)
Let’s make generic formula from the information stated above
Generic formula:
=SMALL (array, ROWS(fixed_cell : varying_cell))
Array : data of numbers
Fixed_cell : cell given as reference. For precaution take the first cell of the data as fixed cell and lock it.
varying _cell : cell reference to vary the kth value for the SMALL function.
Let’s understand this function using it in an example.
Here we have a list of numbers that needed to be sort in ascending
Use the formula:
=SMALL(A2:A21,ROWS($A$2:A2))
$A$2 : locked cell which doesn’t change when copied in excel.
A2 : unlocked cell which changes when copied to other cells.
Explanation:
The value of k here will be 1. The formula drops down to this =SMALL(A2:A21,1) as the ROWS function returns the number of rows which is 1 here..
As you can see, 23 is the smallest number. Now we need this formula to generate increasing value of k for the SMALL function.
Let’s see when we use Ctrl + D shortcut key for copying the formula to other cells.
As you can see we got accurate value of k for every new cell.
The trick is the value of k for the SMALL function. So here we used the ROWS function which returns increasing value of k.
Now we need to get the descending order for the same data.
So now we Use the formula:
=SMALL(A2:A21,ROWS($A$2:A21))
$A$2 : locked cell which doesn’t change when copied in excel.
A21 : unlocked cell which changes when copied to other cells.
Explanation:
The value of k here will be 20. The formula drops down to this =SMALL(A2:A21,20) as the ROWS function returns the number of rows which is 20 here.
As you can see, 87 is the largest number. Now we need this formula to generate decreasing value of k for the SMALL function.
Let’s see when we use Ctrl + D shortcut key for copying the formula to other cells.
As you can see we got accurate value of k for every new cell.
The trick is the value of k for the SMALL function. So here we used the ROWS function which returns decreasing value of k.
As you can see, the SMALL function can sort numbers in both ways.
Hope you understood How to use the SMALL function in Excel. Explore more articles on Mathematical functions like SMALL and SMALL functions here. Please state your query or feedback in the comment box below.
Related Articles:
How to use the LARGE Function in Excel
How to Use RANK in Excel
Sort numbers using Excel SMALL function
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity
How to use the VLOOKUP Function in Excel
How to use the COUNTIF in Excel 2016
How to use the SUMIF Function in Excel
Formula excel small
The Excel SMALL Function
Function Description
The Excel Small function returns the k'th smallest value from an array of numeric values.
The syntax of the function is:
SMALL( array, k )
where the function arguments are:
array    An array of numeric values from which you want to find the k'th smallest value. 
k    The index (i.e. the function returns the k'th smallest value from the supplied array). 
The array argument can be supplied to the function either directly, or as a reference to a range of cells containing numeric values. If values in the supplied range of cells are text values, these values are ignored.
Small Function Examples
The following spreadsheet shows the Excel Small function, used to retrieve the 1st, 2nd, 3rd, 4th and 5th smallest values from the set of values in cells A1A5.
Formulas:
 Results:

Note that in the above example spreadsheet:
 In cell B1, where k is set to 1, the Small function performs the same action as the Excel Min function;
 In cell B5, when k is set to 5 (the number of values in the supplied array), the Small function performs the same action as the Excel Max function.
Further details and examples of the Excel Small function are provided on the Microsoft Office website.
Small Function Errors
If you get an error from the Excel Small Function, this is likely to be one of the following:
Common Errors
#NUM!    Occurs if either:

#VALUE!    Occurs if the supplied k is nonnumeric. 
Also, the following problem is encountered by some users:
Common Problem
The Small function returns the wrong value, or returns the #NUM! error, even though the supplied value of k is between 1 and the number of values in the supplied array.
Possible Reason
Text values, including text representations of numbers within the supplied array, are ignored by the Small function. Therefore, this problem may arise if the values in the supplied array are text representations of numbers, instead of actual values.
Solution
This problem can be solved by converting all array values into numeric values. For details of how to do this, see the Convert Text to Number page.
Return to the Excel Statistical Functions Page
Return to the List of All BuiltIn Excel Functions
Sours: https://www.excelfunctions.net/excelsmallfunction.html
Excel SMALL IF formula to find Nth lowest value with criteria
Trying to get a bottom value based on one or more conditions? Just use the SMALL function together with IF. This tutorial shows how to build a working solution and explains its internal logic, so that you could easily decipher the formula and adjust for your needs.
Microsoft Excel has a number of functions to do "conditional" calculations such as MAXIFS, MINIFS, AVERAGEIF, and the like. Regrettably, the SMALL IF function does not exist. However, nothing prevents you from building your own formula to find the nth smallest value with criteria. If you are not familiar with the Excel SMALL function yet, then you might want to start with the basics and read the above linked tutorial first.
SMALL IF formula in Excel
To get the nth lowest value that matches the criteria you specify, you can use the following generic formula:
Where n is the 1^{st}, 2^{nd}, 3^{rd}, etc. lowest value to return.
For the formula to work correctly, you should enter it as an array formula by pressing the Ctrl + Shift + Enter keys simultaneously. When you do this, Excel will surround the formula in curly brackets like shown in the screenshot below. In Excel 365, it also works as a regular formula due to support for dynamic arrays.
From our sample the table, let's pick bottom 3 scores in a specific subject, say Art. With the list of subjects (criteria_range) in B2:B15, scores in C2:C15 (values) and n in E3, the formula takes this form:
This formula returns the smallest Art score in F3. Copy it down through F5, and you will get the 2^{nd} and 3^{rd} lowest results.
For convenience, you can also input the names of the target subjects in predefined cells (F2  Art and G2  Science), and refer to those cells as criteria:
If the design of your worksheet does not provide for n numbers, you can generate them directly in the formula by using the ROWS function with an expanding range reference like this:
Due to the clever use of absolute and relative references, the range reference expands automatically as the formula is copied to the below cells. In E3, ROWS(A$2:A2) generates n equal to 1, and the formula returns the smallest score for Art. In E4, the reference changes to A$2:A3 causing ROWS to return 2, so we get the 2^{nd} smallest score, and so on.
In case the nth smallest value with specified criteria is not found, the SMALL IF formula would return a #NUM error. To trap this error and replace it with whatever value you find appropriate ("" in our case), you can use the IFERROR function:
How this formula works:
From an array supplied for the 1^{st} argument, the SMALL function returns the nth smallest value specified in the 2^{nd} argument. The problem is that we do not want every single value in the array to be processed, but only the scores in a given subject. To limit the array to Art values, we tell the IF function to compare the list (B2:B15) against the target subject:
Because the logical test is performed on an array of numbers, the result is also an array, where the numbers represent the Art scores, and FALSE values any other scores:
As the SMALL function ignores errors (and anything else that is not a number), the result is the nth smallest value from the Art scores in the above array.
Excel SMALL IF with multiple criteria
To cause a SMALL IF formula to evaluate several conditions, choose one of the following approaches.
Supply multiple criteria by using nested IF statements:
Multiply the logical expressions:
Please notice that both are array formulas, so don't forget to press Ctrl + Shift + Enter to properly complete them. In Excel 365, these will also work as regular formulas.
To test the formula "in the field", we will extend our sample table with the School column and input 2 criteria in separate cells like shown below.
Now, we have all needed parameters for our SMALL IF formula:
 Criteria_range1  list of subjects (B2:B15)
 Criteria1  target subject (G1)
 Criteria_range2  school types (C2:C15)
 Criteria2  target school (G2)
 Values  scores (D2:D15)
 N  numbers 1 to 3 in F6, F7 and F8
Putting the arguments together, we get these formulas:
Enter either one in G6, drag it through G8, and you will get the following result.
How these formulas work:
In essence, both formulas use the IF function to test multiple conditions, so that only the values for which all the conditions are TRUE get into the array argument of the SMALL function.
Nested IFs:
In the logical test of the first IF function, we compare the list of subjects against the target one (Art): $B$2:$B$15=$G$1. The result of this operation is an array of TRUE and FALSE values, where TRUE corresponds to the Art values in column B:
The second IF statement checks which cells in the range C2:C15 match the criterion in G2 (Junior), and also returns an array of TRUE and FALSE:
For the value_if_true argument of the second IF, we supply the scores (D2:D15). This ensures that only the items that have TRUE in the above two arrays "survive"; all other scores are replaced with the FALSE values:
This final array goes to the SMALL function, from which it returns the nth lowest value.
Multiplying criteria:
The second formula checks both conditions within a single logical test, where the multiplication operation works as AND operator.
When the elements of two TRUE/FALSE arrays are multiplied, the logical values are converted to 1's (TRUE) and 0's (FALSE). As multiplying by 0 gives zero, the resulting array has 1 for data that meet both criteria:
The IF function evaluates this array of 1's and 0's in the logical test and passes the scores corresponding to 1's to SMALL.
SMALL IF formula with multiple OR criteria
The previous example shows how to find bottom values based on multiple criteria using AND logic, i.e. when all the conditions are satisfied. To get the smallest values that satisfy any of the conditions, you need to build a SMALL IF formula with OR logic. For this, add up the criteria instead of multiplying them.
As an example, let's pick the lowest scores in two different subjects, say History and Literature. In terms if Excel, the formula will return the nth smallest score if Subject is either History OR Literature.
With the subjects in B2:B15 and scores in C2:C15, here's the formula to return the lowest score:
Of course, you can enter the criteria and n numbers in separate cells, complete the formula by pressing Ctrl + Shift + Enter and get this result:
How this formula works:
The formula's logic is very similar to what is discussed in SMALL IF with multiple AND criteria example. The difference is that the range=criteria equations are joined with the addition operation that works like the OR operator in array formulas:
Checking the list of subjects against 2 different criteria yields 2 arrays of TRUE and FALSE values. Adding the elements of those arrays produces an array of 1's and 0's, where 1's correspond to the items that meet at least one condition (no matter which) and zeros correspond to the items that do not meet any condition. This final array goes to the logical test of IF:
The IF function evaluates each element of the above array and passes the scores corresponding to 1's to the SMALL function, from which it picks the specified nth value:
How to find smallest values ignoring zeros
Though your dataset may sometimes contain some zero values, it does not always make sense to include those zeros in the formula result. To leave out all 0 values, we will be using the already familiar SMALL IF formulas with the "not equal to zero" criteria.
Formula 1. SMALL IF not zero
To get the nth smallest value ignoring 0, use this generic array formula:
Suppose a few students missed some exams and have 0 scores for those subjects. To get the lowest 3 scores that are greater than 0, the formula is:
Please remember to press CTRL + Shift + Enter to complete it correctly.
This formula goes to the topmost cell (E2), into which it extracts the lowest score. And then, you drag the formula down through two more cells to extract the 2^{nd} and 3^{rd} lowest scores. The ROWS(A$2:A2) function automatically generates n numbers, so you needn't type them anywhere in the sheet.
Formula 2. SMALL IF greater than zero with condition
To find the nth smallest value greater than zero based on criteria, place additional criteria in this way:
As an example, let's pull the bottom 3 scores for each subject individually. For this, we enter the subject names in E2 (Art) and F2 (Science) and use this formula to extract the smallest Art scores:
Drag the formula to the right, and you will get the smallest Science scores too:
Filter smallest values based on criteria
This solution only works in recent builds of Excel 365 where dynamic array functions are available.
Another way to get the nth bottom value in Excel based on conditions is using the SMALL function together with FILTER. This works beautifully as a normal formula completed with the Enter key.
The formula's logic is the same as in the previous examples. The difference is that you use the FILTER function instead of IF to apply the criteria.
Formula 1. Find nth bottom value with one condition
If there is just one condition to be met, you can get the nth smallest value with this formula:
For our sample dataset, the formula goes as follows:
Where B2:B15 is the criteria range (list of subjects), C2:C15 are the values (scores), F2 is the criteria (the subject of interest) and E3 is the nth smallest score to return.
Formula 2. Get nth smallest value with multiple criteria
To test multiple conditions, here's the formula to use:
Assuming you are looking to find the nth lowest score in a specific subject (G1) among students of a given school (G2), the formula is:
Where B2:B15 is the list of subjects, C2:C15 are the school types, D2:D15 are the scores, and F6 is the nth smallest value to return.
Formula 3. Filter nth smallest value with OR criteria
To filter the nth smallest number when this or that condition is TRUE, the formula is:
For instance, you can find the lowest score in humanitarian subjects (History or Literature) by using this formula:
Where B2:B15 is the list of subjects, C2:C15 are the scores, E2 and F2 are the subjects of interest, and E6 is the nth smallest value.
That's how to use SMALL IF in Excel to find smallest values based on conditions. I thank you for reading and hope to see you on our blog next week!
Practice workbook for download
Excel SMALL IF examples (.xlsx file)
You may also be interested in:
You will also be interested:
 11 att u verse collection
 Apartments on oak ridge road
 Disney xd sitcoms
 Water tender for sale california
 Wow raid mount
 Gold coffee mug set
 Anime base female
 3 bedroom apartments greenwood
 Small towns near montgomery alabama
 Directv now vs cable
 Jade good fortune necklace
 Best cantonese movies
 Wooden roller coaster track
Listen to me, you lost consciousness yesterday and it's good that you managed to catch Alex, we brought you home and. Called a doctor, the tests showed a terrible thing.  What, I'm pregnant.  barely moving her lips, whispering very slowly.  You fool.