You may have used the MIN and the MAX functions separately in Excel many times. But when it matters to use the functions within the same formula, you might be shattered at times. This tutorial may help you then, here we will show you how to use the MIN and MAX in the same formula.
First things first, let’s get to know about the practice workbook which is the base of our examples.
We have a table consisting of several bills from three cities. Using this table we will see how to use MIN and MAX functions to deal with the maximum and the minimum value at the same time. Please note that the data we are using here is a dummy set of data. The scenario is created to make you understand things simply. In the practical, you may encounter a much larger and more complex data set and scenario.
Basics of MIN – MAX
1. MIN Function
The MIN function returns the least numeric value from a set of values.
MIN (number1, [number2], ...)number1: Number, reference to a numeric value, or range that contains numeric values
number2: Number, reference to a numeric value, or range that contains numeric values.
You can insert as many numbers as you want. Apart from the number1, all are optional. The MIN function ignores empty cells.
To know more about the function, visit the Microsoft Support site.
2. MAX Function
The MAX function returns the largest numeric value from a set of values.
MAX (number1, [number2], ...)number1: Number, reference to a numeric value, or range that contains numeric values
number2: Number, reference to a numeric value, or range that contains numeric values.
You can insert as many numbers as you want. Apart from the number1, all are optional. Similar to the MIN function, the MAX function also ignores empty cells.
To know more about the function, visit the Microsoft Support site.
MIN & MAX in Same Formula
You have understood that the MIN and the MAX return the least and highest value respectively within the given array. So, it’s understandable that you will use the respective function when you need either minimum or maximum.
But when do you need MIN and MAX in the same formula? What does that mean?
When you need to calculate within a range, then using the MIN and MAX functions you can set the least value and the highest value. So, MIN and MAX are in the same formula to set the range. We will do that by using the formula below:
MIN(MAX(value,min_range),max_range)1. Combine MIN & MAX in Same Formula to Get Minimum Percentage Score in Excel
Let’s assume a scenario, where we have a number of students with their scores in Math. Here, the faculty decided to curve the percentages for the failing students (who scored less than 33%). So, we will write the formula so that those who had scored less than 33% will be graced to 33%. Therefore, follow the steps below to use MIN & MAX in the same formula in Excel.
STEPS:
- First, we’ll set the Minimum Percentage as 33%.
- Since the maximum score can be 100, we have set the Maximum Percentage to 100%.
- Now, in cell E5, type the formula:
=MAX(MIN(D5,$D$13),$D$12)- Within the MIN function, we have chosen two values, the student’s percentage and the Maximum Percentage. From here we will find the minimum value.
- Next, the MAX function will compare the returned value of MIN to the Minimum Percentage.
- We have set the Minimum Percentage in the MAX function so that, if anyone scores less than that, then the Minimum Percentage will be the result.
- After that, apply AutoFill to complete the series.
- see the following picture which is our final result.
- You can see the percentages which were less than 33% earlier, now converted to 33%.
2. Generate Monthly Rent by Nesting Excel MIN and MAX in Same Formula
To make you understand the formula better we are assuming another scenario. From the table, let’s imagine a scenario where you have a minimum amount of payment and a maximum amount of payment to make. For each city, the minimum payment will be their respective House Rent. Hence, learn the following steps for applying Excel MIN and MAX in the same formula.
STEP 1: Prepare Dataset
- Firstly, we have set the maximum payment to $4000.
- The minimum payment will be changed with respect to the city name. Here for the city of New York, the Minimum Payment is $2500.
- Similarly, for the city of Chicago, the Minimum Payment will be the House Rent from there ($2200).
- Now, let’s see how much we need to pay within these constraints.
- We have a scenario where you need to pay at least your House Rent with respect to which city you live in.
- Again, if your total rent doesn’t exceed $4000, then you need to pay the total Water Bill, Phone Bill, Internet Bill, and House Rent.
Read More: How to Find Maximum Value in Excel with Condition
STEP 2: Input Formula
- Here, we will set the range (Minimum and Maximum payment) using the MIN and MAX functions.
- However, we need to check whether the sum of all rents exceeds the $4000 mark or not.
MIN(MAX(SUM(rents),Minimum Payment),Maximum Payment)- You may wonder why we have inserted the Minimum Payment within the MAX function.
- Because we need to start the range from there, if for any reason our total payment comes less than the Minimum Payment then the Minimum Payment will be the result of the MAX function.
- And from the MIN function, if the total value exceeds the Maximum Payment then the result will be the amount of Maximum Payment, not the total amount of rent.
- So, in cell E12, input the formula:
=MIN(MAX(SUM(INDEX(C6:E9,,MATCH(C11,C5:E5,0))),C12),C13)- You have noticed we have used the INDEX and MATCH functions here within the SUM function.
- This formula can fetch the value from the city we will select.
- Here, for the city of Chicago, for which we have a minimum of $2200 payment (Equal to House Rent).
- Inside the MAX function, we have made the total rent of this city and compared it with the Minimum Payment. Since it’s higher than the Minimum Payment, the MAX function returned the total.
- Then, within the MIN function, we have the total rent and the Maximum Payment. Comparing these two the MIN function would return the minimum.
STEP 3: Change City
- We’ll change city, and choose Los Angeles.
- For the city of Los Angeles, we have the Minimum Payment of $3500 and the Maximum Payment of $4000.
- Same as previously the MAX function returns the total rent since that will be higher than the Minimum Payment (sole House Rent).
- Total rent for the city of Los Angeles would be – $150+$500+$200+$3500 = $4350.
- Now within the MIN function, we have the total rent and the Maximum Payment. Here the Maximum Payment is the minimum value, so the function will return that amount. Not the total rent.
- You may have doubts about whether only the SUM operation can be done while using MIN-MAX in the same formula. No, not at all. You can do any of your desired operations there.
- From the formula, you might have understood that the concept will be the same, only the presentation is different.
Download Practice Workbook
You are welcome to download the workbook from the link below.
Conclusion
That’s all for today. We have tried to list the use of MIN and MAX in the same formula using a couple of scenarios. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other approaches that we have missed here. You can also comment on the scenario you are stuck with, we are here to help.
