## SMALL function and LARGE function

Returns the k-th smallest value in a data set.

**LARGE(***array*, *k***)**

Returns the k-th largest value in a data set.

**Arguments**

array - Is a reference to a cell range or is an array of constants.

k - The position in the array of the value to return.

### Example 1 - Cell range contains numbers, text and blanks

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.

### Example 2 - Use a condition (Array formula)

The following array formula looks for the second smallest value in column C if corresponding value in adjacent column D is 2012-Dec:

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)

becomes

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

and returns 8.08 in cell F7.

### Download excel *.xlsx file

### Category: Functions

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Excel, Functions, Index

Identify the position of a value in an array.

Comments(12) Filed in category: Excel, Functions

Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function. RAND() function returns a random […]

Comments(10) Filed in category: Excel, Functions

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel, Functions

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]

Comments(8) Filed in category: Excel, Functions, Mod, Quotient

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel, Functions

The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]

Comments(4) Filed in category: Excel, Functions, Transpose

The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]

Comments(4) Filed in category: Excel, Functions, Textjoin

MMULT(array1, array2) Returns the matrix product of two arrays, an array as the same number of rows as array1 and […]

Comments(4) Filed in category: Excel, Functions, Mmult

### 12 Responses to “SMALL function and LARGE function”

### Leave a Reply

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

<code>your formula</code>

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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