Excel: How to Use MIN and MAX in Same Formula

You can use the MIN and MAX functions in Excel to calculate the minimum and maximum values, respectively, for a specific range.

However, sometimes you may wish to use both the MIN and MAX functions in the same formula.

The following example shows how to do so in Excel.

Example: How to Use MIN and MAX in Same Formula in Excel

Suppose we have the following dataset in Excel that contains information about exam scores for various students in some class:

The exam scores should only be allowed to range from 0 to 100, but notice that some scores are below or above these limits.

We can type the following formula into cell C2 to utilize the MIN and MAX functions in Excel to change any values greater than 100 to be equal to 100 and any values less than 0 to be equal to 0:

=MAX(MIN(B2,$B$16),$B$15)

We can then click and drag this formula down to each remaining cell in column C:

Excel MIN and MAX in same formula

If the exam score is between 0 and 100, the formula simply returns the exam score.

However, if the exam score is greater than 100 then the formula returns 100.

Similarly, if the exam score is less than 0, then the formula returns 0.

How This Formula Works

Recall the formula that we used to convert the exam scores to a range between 0 and 100:

=MAX(MIN(B2,$B$16),$B$15)

Here is how this formula works:

First, the formula uses MIN to calculate the minimum value between cell B2 and cell B16.

For example, in the first cell this would return 68.

Next, the formula uses MAX to calculate the max value between 68 and cell B15.

For example, in the first cell this returns 68.

Note: There are many ways to use the MIN and MAX functions in Excel in the same formula. This example simply illustrates one way to do so.