Excel MIN and MAX in Same Formula

You may have used the MIN and the MAX function 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.

Excel sheet - Excel MIN and MAX In Same Formula

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 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 complex data set and scenario.

Practice Workbook

You are welcome to download the workbook from the link below

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 function 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 like below

MIN(MAX(value,min_range),max_range)

To make you understand the formula better we are assuming a few scenarios to show you examples.

Scenario - Excel MIN and MAX In Same Formula

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.

Min -Max payment - Excel MIN and MAX In Same Formula

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.

For the city of Chicago, the Minimum Payment will be the House Rent from there ($2200).

Example of different city - Excel MIN and MAX In Same Formula

Now let’s see how much we need to pay within these constraints.

Constraints - Excel MIN and MAX In Same Formula

We have a scenario where you need to pay at least your House Rent with respect to which city you live in. And if your total rent doesn’t exceed $4000, then you need to pay the total Water Bill, Phone Bill, Internet Bill, and House Rent.

We will set the range (Minimum and Maximum payment) using the MIN and MAX functions.

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

Formula - Excel MIN and MAX In Same Formula

You have noticed we have used the INDEX-MATCH function here within the SUM function. This is why we can fetch the value from the city which we have selected. If you want to know how this has worked, see our previous tutorial about finding the sum with INDEX-MATCH.

Result of formula - Excel MIN and MAX In Same Formula

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.

For this Chicago city, the total rent is lower than the Maximum Payment. So, it returned the amount.

Let’s change the city, and choose Los Angeles. 

Example -Excel MIN and MAX In Same Formula

For the city of Los Angeles, we have the Minimum Payment of $3500 and the Maximum Payment of $4000.

Same as previous 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 whether only 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.

Not only that, you can write the MIN function inside the MAX function (previously we have seen MAX inside the MIN function).

MAX(MIN(value,max_value),min_value)

From the formula, you might have understood that the concept will be the same, only the presentation is different.

Let’s assume a scenario, where we have the number of students with their scores in Math.

Scenario 2 - Excel MIN and MAX In Same Formula

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%. The Minimum Percentage will be 33%.

MAX-MIN formula - Excel MIN and MAX In Same Formula

Since the maximum score can be 100, we have set the Maximum Percentage to 100%.

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.

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

Autofill result - Excel MIN and MAX In Same Formula

You can see the percentages which were less than 33% earlier, now converted to 33%.

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.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo