Excel Tip

SMALL and LARGE Function

Many excel users know that MIN and MAX functions can help you find the lowest or highest value in a given cell range. But did you know about SMALL and LARGE functions? These can help you find the lowest or highest value within a specified position in a cell range. In other words, it can retrieve the “n^th smallest” or “n^th largest” values in a given range.

The syntax for the SMALL and LARGE functions is very simple.

=SMALL (array, n)  array = the range of cells from which to extract smallest or largest value

=LARGE (array, n)  n = the position from the smallest value or the largest value in the array

For example:

=SMALL(B2:B13,3) returns the 3^rd smallest value in the range

=LARGE(B2:B13,2) returns the 2^nd largest value in the range

These two functions are particularly useful when you look for top/bottom 3 or 5 values without sorting the full data range. See the example below:

A note: if the n^th positon you input is larger than the number of values in the select array, the SMALL or LARGE function will return a #NUM! Error message.

Jiazhi Ma
Information, Analytics and Systems, Oakland