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

Get FREE Advanced Excel Exercises with Solutions!

In our day-to-day life, we feel the necessity of finding the highest value as well as the lowest value. We even need to find out a specifically positioned value. For this purpose, we can use the MAX, MAXA, and LARGE functions to find the largest value. On the other hand, we can use the MIN, MINA, and SMALL functions to find the smallest value. In this article, I am going to show a comparison among MAX vs MAXA vs LARGE and MIN vs MINA vs SMALL functions in Excel. I hope it will be helpful for you.


Introduction of MAX, MAXA, LARGE, MIN, MINA, and SMALL Functions

1. MAX Function

The MAX function in Excel is used to return the maximum value from a given range of cells. It is only applicable for numerical values.

Syntax:

=MAX(number1,[number2],...)
Argument Required/Optional Explanation
number1 Required the first number to evaluate
number2 Optional one can list up to 255 additional numbers, for evaluation

For more simplification, I have taken a dataset of examination marks. I want to find the highest marks.

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

Steps:

  • Simply apply the following equation to find the highest value using the MAX function:
=MAX(D5:D9)

Here, the MAX function runs through the cells D5:D9 and returns the highest value.

1. MAX Function

  • Now, press ENTER to have the result.

Thus, we can find out the highest value.


2. MAXA Function

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. It considers TRUE as 1 and FALSE as 0.

Syntax:

=MAXA(number1,[number2],...)
Argument Required/Optional Explanation
number1 Required the first number/argument to evaluate
number2 Optional one can list up to 255 additional numbers/arguments, for evaluation

In this case, I have considered several rows of data including numbers and arguments.

MAXA Function

Steps:

  • Input the following equation to find the highest value using the MAXA function:
=MAXA(B5:D5)

Here, the MAXA function runs through the cells B5:D5 and returns the highest value.

MAXA Function

  • Press ENTER to have the highest value.

MAXA Function


3. LARGE Function

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.

Syntax:

=LARGE(array,k)
Argument Required/Optional Explanation
array Required The array of data to evaluate
k Required The position from the largest value which one wants to return

I have taken a dataset of examination marks to explain the LARGE function. I want to find the third highest marks.

LARGE Function

Steps:

  • First of all, input the following equation to find the third highest value using the LARGE function:
=LARGE(D5:D9,3)

Here, the LARGE function runs through the cells D5:D9 and returns the third highest value as I mentioned the position from the highest value by 3.

  • At the end, hit the ENTER button to have the third highest value.

You can find any place’s value just by changing the value of k.


4. MIN Function

The MIN function returns the smallest or minimum value of a set of values. It is only applicable for numerical values.

Syntax:

=MIN(number1,[number2],...)
Argument Required/Optional Explanation
number1 Required the first number to evaluate
number2 Optional one can list up to 255 additional numbers, for evaluation

To explain the process, I have taken a dataset of examination marks. I want to find the lowest marks.

Steps:

  • Apply the following equation to find the lowest value using the MIN function:
=MIN(D5:D9)

Here, the MIN function runs through the cells D5:D9 and returns the lowest value.

MIN Function

  • To get the lowest value in that range, press ENTER.


5. MINA Function

The MINA function also returns the smallest value from a list of values provided. It can incorporate numbers, text representations of numbers, and logical values. Similar to the MAXA function, it considers TRUE as 1 and FALSE as 0.

Syntax:

=MINA(number1,[number2],...)
Argument Required/Optional Explanation
number1 Required the first number/argument to evaluate
number2 Optional one can list up to 255 additional numbers/arguments, for evaluation

Steps:

  • Firstly, apply the following equation to find the lowest value using the MINA function:
=MINA(B5:D5)

Here, the MINA function runs through the cells B5:D5 and returns the lowest value.

MINA Function

  • Press ENTER to get the lowest value.

  • AutoFill the remaining cells.

MINA Function


6. SMALL Function

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

Syntax:

=SMALL(array,k)
Argument Required/Optional Explanation
array Required The array of data to evaluate
k Required The position from the largest value which one wants to return

I have taken a dataset of examination marks to explain the SMALL function. I want to find the Second lowest marks.

Steps:

  • Input the following equation to find the second lowest value using the SMALL function:
=SMALL(D5:D9,2)

Here, the SMALL function runs through the cells D5:D9 and returns the second lowest value as I mentioned the position from the lowest value by 2.

SMALL Function

  • Now, hit the ENTER button to have the second lowest value.


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

Comparison Among MAX, MAXA, and LARGE Functions

We can calculate the highest number using MAX, MAXA, or LARGE functions. But there is a huge difference in terms of calculating the highest marks. I have shown a comparison in the following section where I have calculated the highest value row-wise. From the comparison, we can come to the points:

  1. Only the MAXA function considers arguments. It considers TRUE as 1 and FALSE as 0. So, it returns the output based on both numbers and arguments. But the MAX and LARGE functions do not consider arguments. They only take numbers into account.
  2. With the LARGE function, we can calculate the Highest number, the second highest number, and so on. But with MAX and MAXA, we can only calculate the highest number.

Comparison Among MAX, MAXA, and LARGE Functions

Read More: How to Find Mean, Median, and Mode on Excel


Comparison Among MIN, MINA, and SMALL Functions

Using MIN, MINA and SMALL functions, we can calculate the smallest number. I have tried to show a comparison in the following section by finding the minimum value row-wise. Applying the formula explained in the above section, we have created this comparison table. According to the result, we can come to the conclusion that:

  1. The MIN and SMALL functions only consider numbers. They do not take arguments into account. But the MINA function considers both numbers and arguments.
  2. We can calculate the lowest number and numbers close to the lowest number using the SMALL function. But with MIN and MINA, we can only calculate the lowest number.

Comparison Among MIN, MINA, and SMALL Functions


Download Practice Workbook


Conclusion

In this article, I have tried to explain a comparison between MAX vs MAXA vs LARGE and MIN vs MINA vs SMALL functions in Excel. I hope it has given you a complete idea of the differences among them. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below.


Related Articles


<< Go Back to Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Taryn Nefdt
Taryn Nefdt

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. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo