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.

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.

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.

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

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

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

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

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.

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

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.

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.