Mehedi Hasan Shimul
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.
Download Practice Workbook
You can download the practice workbook from here.
Introduction to MAX Function
The MAX function simply finds the maximum value from a specified range of data.
- 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.
- 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.
4 Ideal Examples to Understand Difference Between MAX and MIN Functions in Excel
Now, we will discuss the stepwise procedures to find the difference between values from MAX and MIN function 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.
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)
- Then, use the following formula in Cell C13 to calculate the minimum value in range D5:D10.
- 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
Read More: How to Find Minimum Value with VLOOKUP in Excel (6 Ways)
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)
- Finally, use the following formula in Cell C15 to calculate the Difference.
=(C12-C14)+(C14-C13)
Read More: How to Calculate Average, Minimum And Maximum 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)
Read More: How to Use MIN Function to Exclude Zero in Excel (5 Easy Ways)
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")
Read More: How to Use Combined MIN and IF Function in Excel (2 Easy Ways)
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.
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. Visit our ExcelDemy Website for similar articles regarding Excel.
