## How to use 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 from.

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.

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

Cell range B3:B7 contains the following numbers: 2, 3, 5, 5 and 6, cell D3 contains the number that specifies which k-th smallest number to extract.

### Excel Function Syntax

SMALL(*array*, *k*)

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

### Comments

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 that spans over multiple columns like:

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

### Examples

**Table of Contents**

- Cell range contains numbers, text, and blanks?
- Hardcoded array
- How to use SMALL function with criteria?
- SMALL and ROWS function combined?

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

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

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

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

### Articles with the 'SMALL' Function

The following 7 articles have formulas that contain the SMALL function.

SMALL function with duplicates

The formulas in column E, shown in the picture above, extracts the k-th smallest value from B3:B9 ignoring the duplicate numbers. […]

The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that […]

The array formula in column E, shown in above picture sorts text values from column B. The Length columns prove […]

How to ignore zeros using the SMALL function

The formula in cell D3 is an array formula, it will extract the k-th smallest value ignoring zeros. Weekly Blog EMAIL […]

SMALL function – multiple conditions

The array formula in D10 extracts numbers sorted from small to large from column D if Region is equal to […]

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. […]

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 […]

### Functions in 'Statistical'

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

How to use the AVEDEV function

The AVEDEV function calculates the average of the absolute deviations of data points from their mean. The absolute deviation from the […]

How to use the AVERAGE function

Calculates the average of numbers in a cell range. In other words, the sum of a group of numbers and […]

How to use the AVERAGEA function

The AVERAGEA function returns the average of a group of values. Text and boolean value FALSE evaluates to 0. TRUE to […]

How to use the AVERAGEIF function

The AVERAGEIF function returns the average of cell values that are valid for a given condition. Formula in cell D3: […]

How to use the AVERAGEIFS function

The AVERAGEIFS function returns the average of cell values that evaluates to TRUE for multiple criteria. Formula in cell F6: =AVERAGEIFS(D3:D8,B3:B8,F3,C3:C8,G3) […]

How to use the BETA.DIST function

The BETA.DIST function calculates the beta distribution. It represents outcomes in the form of probabilities. This function was introduced in Excel […]

How to use the BETA.INV function

The BETA.INV function calculates the inverse of the cumulative beta distribution. This function has replaced the BETA.INV function and was […]

How to use the BINOM.DIST function

The BINOM.DIST function calculates the individual term binomial distribution probability, use this function when the success probability is constant through […]

How to use the BINOM.INV function

The BINOM.INV function calculates the minimum value for which the binomial distribution is equal to or greater than a given […]

How to use the CHISQ.DIST function

The CHISQ.DIST function calculates the probability of the chi-squared distribution. Use this function to check if a hypothesize is valid. It […]

How to use the CHISQ.DIST.RT function

The CHISQ.DIST.RT function was introduced in Excel 2010 and calculates the right-tailed probability of the chi-squared distribution. It has replaced the […]

How to use the CHISQ.INV function

The CHISQ.INV function was introduced in Excel 2010 and calculates the inverse of the left-tailed probability of the chi-squared distribution. […]

How to use the CHISQ.INV.RT function

The CHISQ.INV.RT function was introduced in Excel 2010 and calculates the inverse of the right-tailed probability of the chi-squared distribution. […]

How to use the CHISQ.TEST function

The CHISQ.TEST function calculates the test for independence, the value returned from the chi-squared statistical distribution and the correct degrees […]

How to use the CONFIDENCE.NORM function

The CONFIDENCE.NORM function calculates the confidence interval for a population mean. Formula in cell C7: =CONFIDENCE.NORM(C3,C4,C5) Excel Function Syntax CONFIDENCE.NORM(alpha,standard_dev,size) […]

How to use the CONFIDENCE.T function

The CONFIDENCE.T function calculates the confidence range for a population mean using a Student's t distribution. Formula in cell C7: […]

How to use the CORREL function

The CORREL function calculates the correlation between two groups of numbers. Formula in cell B12: =CORREL(C3:C9, D3:D9) The value in cell […]

The COUNT function counts all numerical values in an argument, it allows you to have up to 255 arguments. Blank […]

How to use the COUNTA function

The COUNTA function counts the non-empty or blank cells in a cell reference. The picture above demonstrates the COUNTA function […]

How to use the COUNTBLANK function

The COUNTBLANK function counts empty or blank cells in a range. The picture above demonstrates the COUNTBLANK function entered in […]

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

How to use the COUNTIFS function

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

How to use the COVARIANCE.P function

The COVARIANCE.P function calculates the covariance meaning the average of the products of deviations for each pair in two different […]

How to use the COVARIANCE.S function

The COVARIANCE.S function calculates the sample covariance meaning the average of the products of deviations for each pair in two […]

How to use the EXPON.DIST function

The EXPON.DIST function calculates the exponential distribution representing an outcome in the form of probability. This function was introduced in Excel […]

How to use the F.DIST function

The F.DIST function calculates the F probability for two tests. This function was introduced in Excel 2010 and has replaced […]

How to use the F.DIST.RT function

The F.DIST.RT function calculates the right-tailed F probability for two tests. This function was introduced in Excel 2010 and has replaced […]

How to use the F.TEST function

The F.TEST function calculates the two-tailed probability from an F-test, the value shows if the variances from two data sets […]

How to use the FORECAST.LINEAR function

The FORECAST.LINEAR function calculates a value based on existing x and y values using linear regression. Use this function to […]

How to use the FREQUENCY function

Returns how many times values exist in a given range. Note, this function returns an array of values.

The GAMMA function calculates the GAMMA value. It is an extension of the factorial function, the argument is shifted down […]

How to use the GAMMA.DIST function

The GAMMA.DIST function calculates the gamma often used in queuing analysis (probability statistics) that may have a skewed distribution. This […]

How to use the GEOMEAN function

The GEOMEAN function calculates the geometric mean. It represents the typical value of a set of numerical values based on the […]

How to use the GROWTH function

The GROWTH function returns estimated exponential growth based on given data. It calculates the y-values for new x-values based on […]

How to use the INTERCEPT function

The INTERCEPT function returns a value representing the y-value where a line intersects the y-axis. The line is calculated using […]

The LARGE function calculates the k-th largest value from an array of numbers. Use the LARGE function, for example, to extract […]

How to use the LINEST function

The LINEST function returns an array of values representing the parameters of a straight line based on the "least squares" […]

How to use the LOGEST function

The LOGEST function returns an array of values representing the parameters of an exponential curve that fits your data, based […]

How to use the LOGNORM.DIST function

The LOGNORMDIST function calculates the lognormal distribution of argument x, based on a normally distributed ln(x) with the arguments of mean and standard_dev. This […]

The MAX function allows you to calculate the largest number in a cell range. The formula in cell D3 extracts […]

How to use the MAXIFS function

The MAXIFS function allows you to calculate the highest value based on a condition or criteria. Formula in cell G4: […]

How to use the MEDIAN function

The MEDIAN function calculates the median based on a group of numbers. The median is the middle number of a […]

The MIN function allows you to retrieve the smallest number in a cell range. The formula in cell D3 extracts […]

The MINA function returns the smallest number. Text values and blanks are ignored, boolean value TRUE evaluates to 1 and FALSE […]

How to use the MINIFS function

The MINIFS function calculates the smallest value based on a given set of criteria. Formula in cell E3: =MINIFS(C3:C10,B3:B10,"A") The […]

How to use the MODE.MULT function

The MODE.MULT function calculates the most frequent number in a cell range. It will return multiple numbers if they are equally […]

How to use the MODE.SNGL function

The MODE.SNGL function calculates the most frequent value in an array or cell range. Excel Function Syntax MODE.SNGL(number1,[number2],...) Arguments number1 […]

How to use the NORM.DIST function

The NORM.DIST function calculates the normal distribution for a given mean and standard deviation. Formula in cell C7: =NORM.DIST(C2,C3,C4,C5) Excel […]

How to use the NORM.INV function

The NORM.INV function calculates the inverse of the normal cumulative distribution for a given mean and standard deviation. Formula in […]

How to use the PERCENTRANK.EXC function

The PERCENTRANK.INC function calculates the percent rank of a given number in a data set. This function was introduced in […]

How to use the PERCENTRANK.INC function

The PERCENTRANK.INC function calculates the percent rank of a given number compared to the whole data set. The image above […]

How to use the PERMUT function

The PERMUT function returns the number of permutations for a set of elements that can be selected from a larger […]

How to use the PERMUTATIONA function

The PERMUTATIONA function returns the number of permutations for a specific number of elements that can be selected from a […]

The PHI function calculates a number of the density function for a standard normal distribution. Formula in cell C3: =PHI(B3) Excel […]

The PROB function calculates the probability that values in a range are between a given lower and upper limit. Probability […]

How to use the QUARTILE.EXC function

The QUARTILE.EXC function returns the quartile of a data set, use the QUARTILE.EXC function to divide data into groups. This […]

How to use the QUARTILE.INC function

The QUARTILE.INC function returns the quartile of a data set, based on percentile values from 0..1, inclusive. Use the Quartiles.inc […]

How to use the RANK.AVG function

The RANK.AVG function returns the rank of a number in a list of numbers. The number returned indicates its size […]

How to use the RANK.EQ function

The RANK.EQ function calculates the rank of a number in a list of numbers, based on its position if the […]

The SKEW function calculates the skewness of a group of values with an asymmetric tail from its mean value. Formula in […]

The SLOPE function calculates the slope of the linear regression line through coordinates. Formula in cell B10: =SLOPE(B3:B7,C3:C7) Excel Function […]

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

How to use the STANDARDIZE function

The STANDARDIZE function calculates a normalized value from a distribution characterized by mean and standard_dev. Formula in cell C5: =STANDARDIZE(C2, […]

How to use the STDEV.P function

The STDEV.P function returns standard deviation based on the entire population. The standard deviation is how widely numbers are distributed […]

How to use the STDEV.S function

The STDEV.S function returns standard deviation based on a sample of the entire population. The standard deviation is how widely […]

How to use the STDEVA function

The STDEVA function estimates the standard deviation from a sample of values. Standard deviation shows how much the values differ […]

How to use the STDEVPA function

The STDEVPA function returns the standard deviation based on the entire population, including text and logical values. The standard deviation […]

The TREND function calculates values along a linear trend. Fits a straight line (using the method of least squares) to […]

How to use the TRIMMEAN function

The TRIMMEAN function calculates the mean of the interior of a data set. The function excludes a percentage of data […]

The VAR.P function returns the variance based on the entire population. The function ignores logical and text values. Variance shows […]

The VAR.S function tries to estimate the variance based on a sample of the population. The function ignores logical and […]

### 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. […]