Excel LARGE and SMALL Functions

The LARGE and SMALL functions in Excel help us find the n^th highest and lowest values in a list of numbers. While the MAX and MIN functions in Excel can only return the highest and lowest numbers, we can use LARGE and SMALL to return the 2^nd highest number, or the 3^rd lowest number, and so on. For this reason LARGE and SMALL can be much more useful than MAX and MIN in certain situations.

Excel LARGE Function

The LARGE function in Excel returns the nth largest number in a list of numbers, which means you can use LARGE to find the largest number, 2nd largest number, 3rd largest number, etc. It is written as follows:

`=LARGE(cell_range, n)`

Cell Range is the range of consecutive cells for which you are trying to find the nth largest number.
n is the number from the top you want to return. For example, to find the 2nd largest number, n would be 2.

The LARGE function will return an error if the whole range of cells is empty, or if n is negative or larger than the total number of values in the data set.

Excel SMALL Function

The SMALL function in Excel returns the nth smallest number in a list of numbers, which means you can use SMALL to find the smallest number, 2nd smallest number, 3rd smallest number, etc. It is written as follows:

`=SMALL(cell_range, n)`

Cell Range is the range of consecutive cells for which you are trying to find the nth smallest number.
n is the number from the bottom you want to return. For example, to find the 2nd smallest number, n would be 2.

The SMALL function will return an error if the whole range of cells is empty, or if n is negative or larger than the total number of values in the data set.

Examples of the LARGE Function in Excel

	A	B
1	7
2	6
3	4
4	6
5	8

`=LARGE(A1:A5, 2)`

This function returns the 2nd largest number, which would be 7.

`=LARGE(A1:A5, 4)`

This function returns the 4th largest value, which is 6.

`=LARGE(A1:A5, 6)`

This function returns an error because the n argument is larger than the amount of numbers in the range. In other words, there is no 6th largest number.

`=LARGE(A1:A5, 1)`

This function just returns the largest number, which is 8. But if you do this, it might be simpler to just use the MAX function in Excel.

Examples of the SMALL Function in Excel

	A	B
1	7
2	6
3	4
4	6
5	8

`=SMALL(A1:A5, 2)`

This function returns the 2nd smallest number, which would be 6.

`=SMALL(A1:A5, 4)`

This function returns the 4th smallest value, which is 7.

`=SMALL(A1:A5, 6)`

This function returns an error because the n argument is larger than the amount of numbers in the range. In other words, there is no 6th smallest number.

`=SMALL(A1:A5, 1)`

This function just returns the smallest number, which is 4. But if you do this, it might be simpler to just use the MIN function in Excel.

More Examples With the LARGE and SMALL Functions

Say a group of friends went golfing, and because they are nerds they recorded their scores in a table in Excel.

	A	B
1	Golfer	Score
2	Player 1	64
3	Player 2	63
4	Player 3	58
5	Player 4	60

The friends already used the MIN function to figure out who had the lowest score, but now they want to find out who had the second lowest and third lowest scores:

`=SMALL(B2:B5, 2)`

`=SMALL(B2:B5, 3)`

This works to find the 2nd and 3rd lowest scores right now but, as in the other example, the friends want to play golf every weekend and other friends will occasionally join. If more players join, then their functions with the range B2:B5 will no longer be satisfactory, as it may not contain every player's score.

To fix this, the friends can re-write their functions to look at the entire B column. SMALL will ignore cell B1 (because it is text) as well as any empty cells, so each player's score will be analyzed, no matter how many players there are.