MAX vs MAXA vs LARGE and MIN vs MINA vs SMALL Functions in Excel

Excel provides functions to calculate the largest or maximum value in a range and also functions to calculate the smallest or minimum value in a range.

The first function we are going to look at is the MAX Function. This function returns the largest or maximum value, of a set of values. The syntax of the MAX Function is:

Excel MAX function Syntax

If one is using references that contain logical values or text representations of numbers then it is best to use the MAXA Function. The MAXA Function also returns the largest value from a list of values provided, however, the MAXA Function can incorporate numbers, text representations of numbers and logical values. The syntax of the MAXA Function is:

MAXA Function Syntax

The LARGE Function returns the k-th largest value in a list of values. One can use this function to return the first largest value (the same value the MAX Function would return) or the second largest value in a data set or the third largest value in a data set and so on. The syntax of the LARGE Function is:

Excel Large Function Syntax

The MIN Function returns the smallest or minimum value of a set of values. The syntax of the MIN Function is:

Excel MIN Function Syntax

If one is using references that contain logical values that have to be included in the actual calculation, then it is best to use the MINA Function. The MINA Function also returns the smallest value from a list of values provided, however, the MINA Function can incorporate numbers, text representations of numbers and logical values. The syntax of the MINA Function is:

Excel MINA Function Syntax

The SMALL Function returns the k-th smallest value in a list of values. One can use this function to return the smallest value (the same value the MIN Function would return) or the second smallest value in a data set or the third smallest value in a data set and so on. The syntax of the SMALL Function is:

Excel Small Function

So, let’s get started with a few simple examples to illustrate where to use the different functions.

The MAX Function

We have a list of numbers in cell range A4: A50, and we would like to see the values which one is the largest or the maximum value of the data set. So, for this simple evaluation, we can use the MAX Function.

1) So, in cell C4, we enter the following formula:

=MAX(A4: A50)

2) Upon pressing CTRL-ENTER, we get a value of 1006.5 returned.

3) We can check on this by sorting.

4) Select, one of the cells in the range and go to Data>Sort & Filter>Sort Largest to Smallest.

MAX Function Use

5) The values are then sorted from largest to smallest as shown below.

Read More: How to Use Excel ROUND, ROUNDUP, ROUNDDOWN, MROUND & CEILING Functions

The MAXA Function

We have a mixed list of values (numbers and a logical value) in cell range A4: A8 and we would like to see the values, which one is the largest or the maximum value of the data set. So, for this evaluation, we can use the MAXA Function.

1) So, in cell C4, we enter the following formula:

=MAXA(A4: A8)

2) Upon pressing CTRL-ENTER, we get a value of 1 returned, because the logical value of TRUE is read as 1, and thus 1 (the TRUE value) is evaluated to be the largest of this data set.

The LARGE Function

We have a list of numbers in cell range A4: A50, and we would like to see the values which one is the largest or the maximum value of the data set using the LARGE Function. We then want to see the second largest value in the data set and the fourth largest. This is the same dataset, we used in the MAX Function example.

1) We want to first find the largest or maximum value using the LARGE Function so, in cell C4, we enter the following formula:
=LARGE(A4: A50, 1)

2) Upon pressing CTRL-ENTER, we get a value of 1006.5 returned. This is the same value returned by the MAX Function on the data set.

3) We now want to find the second largest value in the dataset, using the LARGE Function so, in cell C5, we enter the following formula:
=LARGE(A4: A50, 2)

4) Upon pressing CTRL-ENTER, we get a value of 1004.5 returned, which is the second largest value in the dataset.

5) We now want to find the fourth largest value in the dataset, using the LARGE Function so, in cell C6, we enter the following formula:
=LARGE(A4: A50, 4)

6) Upon pressing CTRL-ENTER, we get a value of 1002 returned, which is the fourth largest value in the dataset.

The MIN Function

We have a list of numbers in cell range A4: A50 and we would like to see the values, which one is the smallest or the minimum value of the data set. So, for this simple evaluation, we can use the MIN Function.

1) So, in cell C4, we enter the following formula:

=MIN(A4: A50)

2) Upon pressing CTRL-ENTER, we get a value of 0.11 returned.

The MINA Function

We have a mixed list of values (numbers and a logical value) in cell range A4: A8 and we would like to see the values, which one is the smallest or the minimum value of the data set. So, for this evaluation, we can use the MINA Function.

1) So, in cell C4, we enter the following formula:

=MINA(A4: A8)

2) Upon pressing CTRL-ENTER, we get a value of 0 returned, because the logical value of FALSE is read as 0, and thus 0 (the FALSE value) is evaluated to be the smallest of this data set.

The SMALL Function

We have a list of numbers in cell range A4: A50 and we would like to see the values, which one is the smallest or the minimum value of the data set using the SMALL Function. We then want to see the second smallest value in the data set and the third smallest. This is the same data set we used in the MIN Function example.

1) We want to first find the smallest or minimum value using the SMALL Function so, in cell C4, we enter the following formula:
=SMALL(A4: A50, 1)

2) Upon pressing CTRL-ENTER, we get a value of 0.11 returned. This is the same value returned by the MIN Function on the data set.

3) We now want to find the second smallest value in the dataset, using the SMALL Function so, in cell C5, we enter the following formula:
=SMALL(A4: A50, 2)

4) Upon pressing CTRL-ENTER, we get a value of 0.13 returned, which is the second smallest value in the dataset.

5) We now want to find the third smallest value in the dataset, using the SMALL Function so, in cell C6, we enter the following formula:
=SMALL(A4: A50, 3)

6) Upon pressing CTRL-ENTER, we get a value of 0.15 returned, which is the third smallest value in the dataset.

Using the Data Analysis tool in order to get the descriptive statistics of a dataset

One can also get the minimum, maximum, average and other descriptive statistics of a data set using the Data Analysis Tool. You can also see the fourth largest value or second smallest value, for example, using this tool.

We have a range of numbers, and we want to get the entire descriptive statistics for the range. So we can use the Data Analysis Tool in order to do this.

1) First things, first load the Data Analysis Toolpak in Excel.

2) Then go to Data>Analysis>Data Analysis.

3) Select Descriptive Statistics and click Ok.

4) Select A4: A50 as the input range, grouped by Columns. Select $E$4 as the output range on the same worksheet. Check summary statistics in order to get the maximum, minimum and other summary statistics delivered. For the Kth largest value put 4, in order to get the fourth largest value of the data set. For the Kth smallest put 2, in order to get the second smallest value of the data set.

5) Click Ok.

6) The maximum value of 1.275 and the minimum value of 0.082 for the data set is returned. The fourth largest value of 1.175 and the second smallest value of 0.085 of the entire data set, is also delivered as part of the descriptive statistics.

And there you have it.


Crossover Tip (Using These Functions in MS Word)


One can calculate the MAX or MIN of a set of numbers in a Word Table as well. In our Word Table below, we have two columns and we would like to work out the maximum value using the MAX Function in Word, for the first column, and the minimum value of the second column using the MIN Function in Word.

1) In order to work out the maximum for the first column, in the blue shaded cell, go to Table Tools>Layout>Data>Formula.

2) Enter =MAX(ABOVE) and choose a Number Format as shown below.

3) A maximum value for the first column is returned, which is 200 in this case.

4) In order to work out the minimum for the second column, in the orange shaded cell, go to Table Tools>Layout>Data>Formula.

5) Enter =MIN(ABOVE) and choose a Number Format as shown below.

6) A minimum value for the second column is returned, which is 20 in this case.

Download Files

Max-Maxa-Large-Min-Mina-Small

Word-Example

Conclusion

Excel provides a few efficient ways of calculating the minimum or maximum of a data set. The standard functions are the MIN and MAX Function. However, there is also the MINA, MAXA, LARGE and SMALL Functions which extend the functionality of the standard functions. Word also provides a way to calculate the minimum and maximum value in a Table and one can use the Table referencing nomenclature in word to refer to cells or use the ABOVE, BELOW, LEFT nomenclature in one’s word table formulas.

Please feel free to comment and tell us if which of the summary statistical functions you use in your Excel spreadsheets or Word tables.

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS) and biofuels. She enjoys showcasing the functionality of Excel in various disciplines.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

We will be happy to hear your thoughts

Leave a reply

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.