How to Use Excel MIN and MAX in the Same Formula (2 Examples)

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.

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.