Below is the practice workbook, which is the base of our examples.

## 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 the Same Formula

You have understood that the **MIN **and the **MAX** return the least and highest value 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 for setting the range. We will do that by using the formula below:

`MIN(MAX(value,min_range),max_range)`

### Method 1 – Combining MIN & MAX in the Same Formula to Get Minimum Percentage Score

**STEPS:**

- Set the Minimum Percentage as 33%.
- Since the maximum score can be 100, we have set the Maximum Percentage to 100%.

- Enter the following formula in cell
**E5**:

`=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, the*Minimum Percentage*will be the result.

- Apply
**AutoFill**to complete the series. - See the following picture, which is our final result.
- You can see the percentages less than 33% earlier, now converted to 33%.

### Method 2 – Generate Monthly Rent by Nesting Excel MIN and MAX in the Same Formula

#### STEP 1: Prepare Dataset

- Set the maximum payment to $4000.
- The minimum payment will be changed according to the city name. 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). - Let’s see how much we need to pay within these constraints.

- We have a scenario in which you need to pay at least your House Rent in the city you live in.
- 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

- Set the range (Minimum and Maximum payment) using the
**MIN**and**MAX**functions. - Check whether the sum of all rents exceeds the $4000 mark.

`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 result from the**MAX**function. - From the
**MIN**function, if the total value exceeds the*Maximum Payment,*the result will be the Maximum Payment amount, not the total amount of rent. - Enter the following formula in cell E12:

`=MIN(MAX(SUM(INDEX(C6:E9,,MATCH(C11,C5:E5,0))),C12),C13)`

- 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,*we have a minimum payment of $2200 (Equal to House Rent). - Inside the
**MAX**function, we calculated this city’s total rent and compared it with the Minimum Payment. Since it was 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

- Change city and choose
*Los Angeles.* - For the city of
*Los Angeles*, the Minimum Payment is $3500, and the Maximum Payment is $4000. - The
**MAX**function returns the total rent since that will be higher than the*Minimum Payment*(sole House Rent). *Total rent for Los Angeles would be – $150+$500+$200+$3500 = $4350.*- 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 doubt whether the SUM operation can be done only 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 remain the same; only the presentation will be different.

**Download Practice Workbook**

Download the workbook from the link below.

## Related Articles

- How to Set a Minimum and Maximum Value in Excel
- Find Max Value and Corresponding Cell in Excel
- How to Cap Percentage Values Between 0 and 100 in Excel
- How to Find Max Value in Range with Excel Formula

**<< Go Back to Excel MAX Function | Excel Functions | Learn Excel**