Sometimes we need to put MIN and MAX in the same formula in Excel. In this article, we are going to show you how to put MIN and MAX in the same formula in Excel with a suitable example. We have also included some relevant concerns at the end of this article. So, let’s get started.
📂 Download Excel File
Download the free workbook from here:
Overview of MAX and MIN Functions
Firstly, you need to know the definitions of the MIN Function and MAX Function in Excel. These two functions are very important features of Excel.
MIN Function
The MIN function is a primary Excel function which we also know as the Statistical function of Excel. The MIN function is basically utilized to find the minimum or smallest value from a supplied set of data. We basically use the below formula to define the MIN function.
=MIN(number1,[number2], ….)
The numbers in the above syntax represent the numeric values, arrays, named range, a reference to a specific cell, or a range including the numbers.
MAX Function
The MAX Function is a primary Excel which we also know as a Statistical Function. It is typically utilized to find the maximum or largest value from a supplied set of data. Generally, we use the below-mentioned formula to define the MAX function.
=MAX(number1,[number2], ….)
Here, the numbers represented numeric values, arrays, named ranges, a reference to a specific cell, or a range including the numbers.
How to Use MIN and MAX in Same Formula in Excel
While writing this article, “Excel MIN and MAX in Same Formula”, we’ve considered a dataset that contains approximately 4 columns and 14 rows. In this dataset, we added City, Humidity, and Final Humidity to make you better understand. But if you want, you can change the entities and put the values of your own.
We often use the MAX function and MIN function separately. But this time we are going to use these two functions combinedly in the same formula. And the formula will look like:
=MIN(MAX(value,min_range),max_range)
You will easily understand the procedures if you see the examples given below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, we have set the minimum humidity to 39% and maximum humidity to 85%.
- Secondly, select cell D6.
- Thirdly, put the given formula there.
=MAX(MIN(C6,$D$14),$D$13)
🔨 Formula Breakdown
We will find the minimum value from the MIN function and
As we have set the Minimum Humidity to the MAX function, when the humidity goes down the Minimum Humidity will be the resultant humidity.
- Now press Enter.
- Fourthly, using the Fill Handle, drag the formula down to the other cells.
- Eventually, you will get your desired result.
- For cell D11, you will see the final humidity has changed to 39% as we set the Minimum Humidity to 39%.
- Consequently, you can achieve Excel MIN and MAX in same formula using the formula mentioned above.
How to Create Formula with MAX Function in Excel
One of the basic formulas of Excel is the MAX formula. You can obtain this formula by maintaining the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell C13.
- Then write the given formula on that cell.
=MAX(C6:C11)
- Now tap Enter.
- Eventually, you will get the maximum value from the given data.
Finally, using MAX formula is a very useful formula to find the maximum values from a set of data in Excel.
How to Create Formula with MIN Function in Excel
If you want to find the minimum value from a set of given data, then this part is for you. You can easily obtain this by using the MIN formula.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, select cell C13.
- Secondly, write the following formula on that cell.
=MIN(C6:C11)
- Now press Enter.
- Consequently, you will find the minimum value there.
Finally, applying the MIN formula is very user-friendly to find the minimum value from a set of given data in Excel.
Apply Formula So That Value Does Not Exceed a Certain Limit in Excel
Sometimes we need formula value in Excel not to exceed a certain limit. You can easily achieve this by maintaining the following steps given below.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cell D6.
- Secondly, write down the following formula on that cell.
=MIN(0.7*C6,100%)
- Here the formula implies that the maximum value should not exceed 72%.
- Now press Enter.
- Thirdly, using Fill Handle, drag the formula down to the entire column.
- Eventually, you will get all the values not exceed the limit.
Finally, this is a very simple formula in Excel not exceed the limit.
How to Calculate Average Value in Excel
In Excel, there is an important feature that can calculate the average value from a set of given data. You just need to maintain the following steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, go to the AVG Formula sheet.
- Secondly, select cell C13.
- Thirdly, put the formula on that cell.
=AVERAGE(C6:C11)
- Now tap Enter.
- Finally, you will get your desired value.
Eventually, this formula is very unique to getting the average value in Excel.
Find Maximum Value with Criteria Combining MAX and IF Functions in Excel
If you want to find the maximum value in Excel with criteria, then you can follow this portion. The steps are quite easy and simple.
⬇️⬇️ STEPS ⬇️⬇️
- Primarily, you need to select cell C14.
- Secondly, you need to write the following formula on that cell.
=MAX(IF(B6:B11=B14,C6:C11))
🔨 Formula Breakdown
Here:
MAX is for the maximum value we want to find and
IF is for the condition to check the values from data.
- Now write the required name of the City on cell B14.
- Now press Enter.
- Finally, you will find the required value.
Eventually, the MAXIF Function is a very handy function to find value in Excel with criteria.
Find Minimum Value with Condition Combining MIN and IF Functions in Excel
In Excel, if you want to find the minimum value with the condition then you need to follow the below-mentioned steps.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, select cell C14.
- Now write the formula on that cell.
=MIN(IF(B6:B11=B14,C6:C11))
- Now write the required city on cell B14.
- Press Enter.
- Consequently, you will get the desired result.
Lastly, you use this MINIF Function to find the minimum value with the condition.
📄 Important Notes
You need to be careful while performing the above-mentioned procedures.
🖊️
You should be conscious while putting MIN and MAX functions in the same formula.
🖊️ You need to be careful when you intend to use the Excel functions.
📝 Takeaways from This Article
If we summarize the whole article, we have got some points.
📌 Firstly, we put the MIN and MAX functions in the same formula in Excel.
📌
Secondly, we used the MIN formula in Excel.
📌 Thirdly, we applied the MAX formula in Excel.
📌
Fourthly, we adopted an Excel formula value not exceeding the limit.
📌 Fifthly, we calculated the Average value in Excel.
📌
Finally, we used MAXIF and MINIF formulas in Excel.
Conclusion
We wish that you would be able to put MIN and MAX functions in same formula in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any skepticism in your mind, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.