The MIN and MAX functions allow you to identify the lowest and highest values within a range. But what if you want to find the second, or third highest values? Well there are two companion functions that you can use to do just that.

It is useful, when looking at large data sets, to get a feel for the highs and lows in values as well as the spread of values in a range.

We can also use statistical calculations like average, median and the standard deviation to get a feel for the numbers.

In the case of outliers, those values at either end of the values, it can be useful to see what they are and how they are spread. For example how closely grouped are the top or bottom ten values.

Again you can sort your data to see the span of values. You might want to use a formula. Maybe you are comparing a number of different data sets, then the functions you need are SMALL and LARGE.

SMALL and LARGE functions

Both functions have the same syntax.

SMALL(Range,Number)

LARGE(Range,Number)

The Range is the range of values to review. The range is usually a fixed reference.

The Number represents the number position you want to extract eg 3 would extract the third lowest (SMALL) or the third highest (LARGE).

Always remember there could be ties/draws. You can link the Number to a cell as we will do in the example below.

In the example below we want to find the top three, and bottom three values from the values in columns A and B.

Cells D2:D4 contain the numbers 1 2 and 3. These are used by the functions in E2:F4 to help calculate the top and bottom values.

The formula for cell E2 is

=SMALL($A$2:$B$10,D2)

This has been copied down into the cells below

The formula for cell F2 is

=LARGE($A$2:$B$10,D2)

And this has also been copied down the column.

The lower values are closely grouped.

In terms of the upper values you can see there are large differences in these high outliers.

When used with ranges that contain dates you can find the earliest and latest dates.