Excel Formula: Specify Max Value Not to Exceed

You can use the following formula in Excel to specify the maximum value that can be returned by a formula:

=MIN(300,(SUM(B2:D2)))

This particular formula calculates the sum of values in the range B2:D2, but if the sum is greater than 300 then the formula simply returns 300.

The following example shows how to use this formula in practice.

Example: Specify Max Value Not to Exceed in Excel

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

Suppose we would like to calculate the sum of exam scores for each student but we want the maximum value to be set at 300.

We can type the following formula into cell E2:

=MIN(300,(SUM(B2:D2)))

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

Excel formula max value not to exceed

The formula either returns the sum of exam scores for each student or the value 300 if the sum is greater than 300.

For example:

The sum for Andy is 90 + 101 + 115 = 306, so the formula returns 300.
The sum for Bob is 88 + 95 + 90 = 273, so the formula returns 273.
The sum for Chad is 90 + 93 + 91 = 274, so the formula returns 274.

And so on.

How This Formula Works

Recall the formula that we used to limit the maximum value that could be returned to 300:

=MIN(300,(SUM(B2:D2)))

This formula uses the MIN function to find the minimum value between the value 300 and the result of the sum of cells B2, C2 and D2.

This formula is guaranteed to return either the value 300 or a value that is less than 300 if the sum of cells B2, C2 and D2 are less than 300.

This has the effect of setting 300 to be the max value that the formula can return.