SMALL & LARGE functions

The SMALL function returns the k-th smallest number from an array of numbers.

The SMALL function syntax is as follows:

= SMALL ( array; k )

Array can be any row, column, or a combination of both.

k is an integer number representing the position of a number in the array if data in that array would be sorted from the smallest.

 

Consider the following example:

small function

If k is 1, the smallest number will be returned.

If k is the same number as the total count of numbers in an array, the largest number will be returned.

If k is zero, smaller than zero, or larger than the total count of numbers in an array, error will be returned.

The SMALL function is useful if we want to return the k-th (2nd, 3rd) smallest number in an array.

If the smallest numbers are repeating, 2nd, 3rd etc. smallest numbers will be the same as the first.

 

 

The LARGE function returns the k-th largest number from an array of numbers.

The LARGE function syntax is as follows:

= LARGE ( array; k )

Array can be any row, column, or a combination of both.

K is an integer number representing the position of a number in the array if data in that array would be sorted from the smallest.

 

Consider the following example:

large function

If k is 1, the largest number will be returned.

If k is the same number as the total count of numbers in an array, the smallest number will be returned.

If k is zero, smaller than zero, or larger than the total count of numbers in an array, error will be returned.

The LARGE function is useful if we want to return the k-th (2nd, 3rd) largest number in an array.

If the largest numbers are repeating, 2nd, 3rd etc. largest numbers will be the same as the first.

 

Dig deeper:

Numbers in Excel

One thought on “SMALL & LARGE functions”

Leave a Reply

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