# How to use the SMALL function

**What is the SMALL function?**

The SMALL function returns the k-th smallest value from a group of numbers. The first argument is a cell range or array that you want to find the k-th smallest number in.

The second and last argument is k which is a number from 1 up to the number of values you have in the first argument.

### SMALL function example

Example shown in the above image, formula in cell E3 returns 17 because it is the third smallest number in cell range B3:B11.

Cell range B3:B1 contains the following numbers: 65, 50, 17, 22, 20, 66, 13, 18, and 15. Cell D3 contains the number that specifies which k-th smallest number to extract.

### Table of Contents

## 1. SMALL Function Syntax

SMALL(*array*, *k*)

## 2. SMALL Function Arguments

array |
Required. A group of numbers you want to extract theÂ k-th smallest numberÂ from. |

k |
Required. k-th value, 1 returnsÂ the smallest number, 2 returns the second smallest number etc. |

The SMALL function is very versatile and is, in my opinion, one of the most used functions in Microsoft Excel. You can construct both regular and array formulas with the SMALL function.

It also ignores blank values and text values, however, not error values. This article explains a workaround if you have error values in your data set.

How to ignore error values using the SMALL function

You can use a cell range across multiple columns like:

It will also work with multiple non-adjacent cell rangesÂ with minor changes to the formula.

## 3. How does the SMALL function handle text and blank values?

The image above shows a formula in cell B13 that extracts the third smallest value from cell range B3:B10. Note that the cell range contains both text values and blank cells.

becomes

SMALL({3; 6; "A"; 5; "X"; 0; 4; 4}, 3)

Text strings and blanks are overlooked. The array becomes

SMALL({3; 6; ; 5; ; ; 4; 4}, 3)

and returns 4. 4 is the third smallest numerical value in the array.

## 4. How to use constants (hardcoded) values in the SMALL function

In case you want to work with an array instead of a cell range in the SMALL function use curly brackets like this:

This means that the values are hardcoded into the formula, however, you still enter it as a regular formula.

There is one downside with this approach and that is that you must edit the formula to be able to change a value in the array.

To convert a cell range to an array select the cell reference in the formula and press function key F9.

This will convert the cell range to an array of values.

## 5. How to use a condition in the SMALL function

This array formula in cell F7 calculates the second smallest number from cell range C4:C14 based on a condition specified in cell F3.

The IF function returns one value if the logical test returns TRUE and another value if the logical test is FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

In this case, the IF function compares the values in cell range B4:B14 to the value in cell F3 and returns and an array that contains boolean valuesÂ TRUE or FALSE.

SMALL(IF(B4:B14=F3, C4:C14, ""), F4)

becomes

SMALL(IF({"2013-Jan"; "2012-Dec"; "2013-Jan"; "2012-Dec"; "2012-Nov"; "2013-Jan"; "2013-Jan"; "2012-Dec"; "2013-Jan"; "2012-Nov"; "2012-Dec"}="2012-Dec", C4:C14, ""), F4)

becomes

SMALL(IF({FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}, C4:C14, ""), F4)

The IF function then returns the corresponding value from the second argument if TRUE and the third argument if FALSE.

SMALL(IF({FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE}, C4:C14, ""), F4)

becomes

SMALL(IF({FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE},{55.47; 60.69; 5.83; 8.08; 94.68; 70.79; 41.03; 24.44; 81.68; 72.67; 2.25},""), F4)

becomes

SMALL({"";60.69;"";8.08;"";"";"";24.44;"";"";2.25}, 2)

and returns 8.08 in cell F7.

## 6. How to quickly return sorted numbers using the SMALL function

Formula in cell E3:

The second argument k can be changed from a number to a function that returns numbers, this can be handy when you want to return multiple numbers sorted from small to large.

SMALL(*array*, *k* )

The ROWS function returns the number of rows a cell range contains. If you combine absolute and relative references into one cell reference you can build a dynamic cell reference that changes when you copy the cell and paste to cells below.

$A$1:A1

The first part of the cell reference is absolute meaning it won't change when the cell is copied and pasted to cells below. You can see that it is absolute bu the $ dollar signs in front of the column letter and the row number.

The colon is used to describe a cell range that contains multiple cells however it can also describe a reference to a single cell. The second part is relative meaning it will change when you copy the cell.

For example, the table below demonstrates how the cell references in the formula change when copied.

Cell E3: =SMALL($B$3:$B$11, ROWS($A$1:A1))

Cell E4: =SMALL($B$3:$B$11, ROWS($A$1:A2))

Cell E5: =SMALL($B$3:$B$11, ROWS($A$1:A3))

The cell range expands by one row for each new cell below you paste it to. The ROWS function calculates the number of rows in that cell range and returns that number.

Cell E3: =SMALL($B$3:$B$11, 1)

Cell E4: =SMALL($B$3:$B$11, 2)

Cell E5: =SMALL($B$3:$B$11, 3)

You can press and hold on the black dot in the bottom right corner of the selected cell then drag down as far as needed to quickly copy the cell to cells below, see animated image above.

You can also double press with left mouse button on with left mouse button on the black dot located in the bottom right corner of the selected cell to quickly copy the cell to cells below.

Excel uses existing values in the adjacent column to determine when to stop copying.

## 7. SMALL function with multiple cell ranges

Today I learned how to sort numbers from multiple cell ranges thanks toÂ Sam Miller. It is surprisingly simple and easy.

Formula in cell H3:

The SMALL function ignores text and blank cells, however, not error values.

### Explaining formula in cell H3

#### Step 1 - Enable multiple cell ranges

The first argument in theÂ SMALL function is the *array* parameter: SMALL(*array*, *k*).

Use parentheses to enable multiple cell ranges in the first argument.

($B$3:$B$8, $D$3:$D$6, $F$3:$F$7)

The ,Â (comma) separates the cell references.

#### Step 2 - Extract k-th smallest number

The second argument allows you to specify which number to extract based on their sort order.

ROWS($A$1:A1)

The ROWS function allows you to insert new rows and columns in your worksheet without breaking the formula.

The cell reference contains two parts, one is an absolute cell reference and the other is a relative cell reference.

The $ sign allows you to specify an absolute cell reference, this cell reference does not change when you copy the formula to cells below.

### Get Excel *.xlsx file

SMALL function with multiple cell ranges.xlsx

## 8. How to ignore error values using the SMALL function

The image above shows you a formula in cell D3 that tries to get the smallest number from cell range B3:B12 but it returns an error. This happens if the cell range contains at least one error value.

Formula in cell D3:

The SMALL function ignores text and boolean values but not error values, however, the AGGREGATE function lets you choose between a variety of functions (including SMALL function).

You also have the option to ignore errorÂ values, the second argument lets you specify this.

The AGGREGATE function contains these functions AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV.S, STDEV.P, SUM, VAR.S, VAR.P, MEDIAN, MODE.SNGL, LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC,Â PERCENTILE.EXC and QUARTILE.EXC.

You can see a list of available functions while entering the arguments in the function, see image below.

The second argument has the following settings, I chose 6 - Ignore error values.

The AGGREGATE function was introduced in Excel 2010, if you have an earlier Excel version then I recommend using the following array formula:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### 'SMALL' function examples

This post explains how to lookup a value and return multiple values. No array formula required.

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

### Functions in 'Statistical' category

The SMALL function function is one of 74 functions in the 'Statistical' category.

### Excel function categories

### Excel categories

### 12 Responses to “How to use the SMALL function”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Hi Oscar,

I am in love with.. your formula explanation.. :)

Waiting eagerly for MMULT & some D-Functions..

Regards,

Deb

Debraj Roy,

Thank you!

I am curious, in what situation do you use MMULT?

Hi Oscar,

We can use MMULT in all cases where SUMPRODUCT fails..

with only Two Criteria..

* Only TWO Array can be multiplied..

* 1st Array's No Of Row.. Should be Same as 2nd Array's No Of Column..

Unlike SUMPRODUCT, It returns ARRAY output..

I think, Binary Addition & Binary Multiplication are the base of all FORMULA's & FUNCTION..

and you are doing a great job, by teaching/using them in your daily blog..

Regards!

Deb

Debraj Roy,

Well, I am learning from you right now.

Can you provide an example where SUMPRODUCT fails and MMULT succeeds?

I searched and found my old mathematics books from college, I had forgotten the basics of multiplying two matrices. :-)

It is worthwhile mentioning that in both Small and Large K could also be an array

So if A1:A10 contains random numbers the below formulas

=Large(A1:A10,{1,2,3}) - Return an array containing the top 3 numbers

=SUM(Large(A1:A10,{1,2,3}) -Array entered Returns the Sum of the top 3 numbers

=SUM(LARGE(A1:A10,ROW(INDIRECT("1:"&TopN))))- Array Entered Returns the sum of the Top N numbers as defined in the Cell/Named Constant TopN

=Large(A1:A10,Row(A1:A10))- Array entered returns an array of numbers in A1:A10 in Descending order

Likewise Small

sam,

It is worthwhile mentioning that in both Small and Large K could also be an arrayYes you are right! Thanks for pointing that out.

[…] SMALL(array,k) Returns the k-th smallest number in this data set. […]

=Small({VALUE(DV147),VALUE(DZ147),VALUE(ED147),VALUE(EH147)},2) will not work. If I use sum and the "Value(-----)" amounts, it works.

What am I doing wrong?

The numbers are stored as text in those cells for other reasons.

[…] LARGEÂ function extracts the n-th largest number twice for every two cells. This allows us to return both the […]

I'm using the SMALL function inside an array. I understand how to use the function to return an array where values are greater than or equal to a number. But how do I use the function if I want to return results that are between two numbers?

I've tried nesting an AND statement within the IF statement, but it isn't working (no values are returned).

Any suggestions? Thanks!

julie,

=SMALL(IF(($A$2:$A$10<$F$2)*($A$2:$A$10>$F$3),$A$2:$A$10,""),ROW(A1))

[…] LARGE(array,k) returns the k-th largest row number in this data set. […]