How to Use MIN and MAX Functions in Same Formula in Excel

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.

DEFINITIONS OF FUNCTIONS

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.

DEFINITIONS OF FUNCTIONS

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.

Dataset to Excel MIN and MAX in same formula

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.

Example to Excel MIN and MAX in same formula

  • 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%.

Result to Excel MIN and MAX in same formula

  • 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.

Use of MAX formula in Excel to Excel MIN and MAX in same formula

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.

Result of MIN formula in Excel

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.

Implementation of value not exceed in Excel

  • Thirdly, using Fill Handle, drag the formula down to the entire column.

  • Eventually, you will get all the values not exceed the limit.

result of formula value not exceed

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.

Result of average formula

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.

Result of MAXIF formula

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.

 

Result of MINIF formula

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.

(Visited 117 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo