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

**Table of Contents**hide

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

#### 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-MATCH function**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.

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