Difference Between MAX and MIN Function in Excel

Get FREE Advanced Excel Exercises with Solutions!

The MAX and MIN functions are very frequently used in Excel. These functions have a wide range of uses. In this article, we determine the difference between the result of MAX and MIN function in Excel with 4 ideal examples.


Introduction to MAX Function

The MAX function simply finds the maximum value from a specified range of data.

MAX function

  • Syntax:
=MAX(number1,[number 2],..)
  • Arguments:
ARGUMENT REQUIRED/OPTIONAL EXPLANATION
number1 Required It is the first number to determine the maximum value.
[number2] Optional It is the second number to determine the maximum value.

Similarly, we can add more numbers as arguments.

  • Return Value:

The function returns the maximum value among the numbers in the arguments.

  • Version:

The MAX function is available in all versions of Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and lower.


Introduction to MIN Function

The MIN function simply finds the minimum value from a specified range of data.

MIN function

  • Syntax:
=MIN(number1,[number 2],..)
  • Arguments:
ARGUMENT REQUIRED/OPTIONAL EXPLANATION
number1 Required It is the first number to determine the minimum value.
[number2] Optional It is the second number to determine the minimum value.

Similarly, we can add more numbers as arguments.

  • Return Value:

The function returns the minimum value among the numbers in the arguments.

  • Version:

The MIN function is available in all versions of Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007, and lower.


Difference Between MAX and MIN Function in Excel: 4 Ideal Examples

Now, we will discuss the stepwise procedures to find the difference between values from MAX and MIN functions in Excel following different ways with 4 ideal examples.


1. Use Subtract Operator with MAX and MIN Functions to Calculate Difference

The first example is quite simple. We will subtract the minimum value from the maximum value using the minus () operator. For demonstration, we have included some Sales amount and we will calculate the difference of the maximum and minimum values from them.

dataset for difference between max and min function in excel

Follow the given steps for the example.

  • Firstly, apply the following formula in Cell C12 to calculate the maximum value in range D5:D10.
=MAX(D5:D10)

Finding maximum value

  • Then, use the following formula in Cell C13 to calculate the minimum value in range D5:D10.

finding minimum value

  • Finally, simply subtract the value of Cell C13 from value of Cell C12.
  • For that, write the following formula in Cell C14 and press Enter.
=C12-C13

finding difference between max and min function in excel

Read More: How to Find Minimum Value with VLOOKUP in Excel


2. Calculate Difference Between Values Using MAX and MIN with AVERAGE Function

In this example, we will use the AVERAGE function to calculate the average of the data. We will use a bit modified formula to get the difference between the maximum and minimum values. The general formula is:

Difference = (Maximum-Average)+(Average-Minimum) = Maximum-Minimum
  • Firstly, find the maximum and minimum value in Cells C12 and C13 as before.
  • Then, apply the following formula in Cell C14 to get the average of the data.
=AVERAGE(D5:D10)

finding average

  • Finally, use the following formula in Cell C15 to calculate the Difference.
=(C12-C14)+(C14-C13)

Calculating Difference Between Values Using MAX and MIN with AVERAGE Function

Read More: How to Find Minimum Value in Excel


3. Find Difference with Single Condition Using MAX and MIN Functions

Now, we will explain a trickier example. Here, we will apply the IF function for getting the difference between the maximum and minimum values if the difference is less than 500. Otherwise, the formula will return 500 as difference. Follow the steps for this example.

  • Firstly, find the maximum and minimum values in Cells C12 and C13 as before.
  • Then, apply the following formula in Cell C14 to get the difference value there.
=IF((C12-C13)<500,C12-C13,500)

Finding Difference with Single Condition Using MAX and MIN Functions

The formula returned 500 as the difference because the real difference of maximum and minimum is 575 which is greater than 500. So, the IF function returns the False value which is 500.

Read More: How to Use MIN Function to Exclude Zero in Excel


4. Determine Difference Based on Multiple Conditions Using MAX and MIN Functions

Instead of one condition, we can also apply multiple conditions for determining maximum value, minimum value and their difference. In the following example, we will use a formula that will return the difference between the maximum value and minimum value if the difference is in between the range of 400 to 500. Otherwise, it will show “Not Applicable”. Follow the steps given below for this example.

  • Firstly, find the maximum and minimum values in Cells C12 and C13 as before.
  • Then, apply the following formula in Cell C14 to get the difference value with multiple conditions there.
=IF(((C12-C13)<500)*((C12-C13)>400),C12-C13,"Not Applicable")

result of difference between max and min function in excel

In the formula, ((C12-C13)<500)*((C12-C13)>400) these are two combined conditions for the IF function and C12-C13 is the True value and Not Applicable is the False value.

Read More: How to Use Combined MIN and IF Function in Excel


Things to Remember While Using MIN and MAX Functions in Excel

  • If we include numbers as text values in the argument of MAX or MIN function, the function will ignore the cell.
  • Also, the MAX or MIN don’t count blank cells as 0.

Download Practice Workbook

You can download the practice workbook from here.


Conclusion

In this article, we have covered the procedures to determine the difference between MAX and MIN function in Excel. Hope, it will be useful to you. If you have any suggestions or queries, let us know by commenting.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo