Excel – MIN & MAX vs. SMALL & LARGE Functions

The MIN and MAX functions are two of Excel’s basic functions to find the single lowest or highest value in a range of cells and they appear in the drop down list on the AUTOSUM button.

Drop down next to AUTOSUM

Many beginners often ask in training sessions when they would be likely to use these functions, but once you know them, it’s surprising how much use you can actually get out of them.

The other two functions, SMALL and LARGE also return lowest and highest values respectively but with the added option to find the 2nd , 3rd, 4th, 5th lowest/highest ….etc.

The basic syntax for MIN and MAX is;

=MIN(range to test)

=MAX(range to test)

If we look at a basic table with a range of values…

Basic table to use MIN, MAX etc.

You might be able identify the lowest or highest values as this is only a small number of cells, but try doing that with 1,000s or more – it would be very easy to get the wrong answer.

In this example, to find the lowest single value our formula would be;

=MIN(A2:C15) giving us the answer 45.

To get the highest;

=MAX(A2:C15) giving us the answer 975.

Simple, quick…enough said.

Now let’s take a look at SMALL and LARGE.

The basic syntax for these is;

=SMALL(range to test, nth value)

=LARGE(range to test, nth value)

Returning to our data table above, we could extract the following lowest values;

=SMALL(A2:C15,1)
giving us the answer 45…exactly the same as MIN i.e. lowest value

=SMALL(A2:C15,3) giving us the answer 124 (3rd lowest value)

=SMALL(A2:C15,12) giving us the answer 239 (12th lowest value)

And to get the largest values;

=LARGE(A2:C15,1)
giving us the answer 975…exactly the same as MAX i.e. highest value.

=LARGE(A2:C15,5) giving us the answer 863 (5th highest value)

=LARGE(A2:C15,9) giving us the answer 787 (9th highest value)

So there you have finding lowest and highest values. As with all Excel functions, combining these with other functions will give you a lot more power to analyse and find values in your spreadsheets. Look out also for my blogs on DATE and TEXT functions.

 

Advertisements

Posted on November 10, 2014, in Functions & Formulas and tagged , , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: