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:

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.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Excel:

Excel: How to Highlight Max Value in Each Row

Excel: How to Create Min Max and Average Chart

Excel: How to Find Max Value by Group