Summary

To cap a calculated percentage at 100%, you can use the MIN function. In the example shown, the formula in D6 is:

=MIN(1,B5/C5)

which guarantees the result will never exceed 100%.

Generic formula

=MIN(1,A1/B1)

Explanation 

This formula uses the MIN function as an alternative to the IF function.

Although MIN is frequently used to find the minimum value in a larger set of numbers, it also works fine with just two values.

Inside MIN, the first value is hardcoded as 1, the equivalent of 100% when formatted as a percentage. The second value is the result of B5 divided by C5. The MIN function simply returns the smaller of the two values:

  • When B5/C5 is < 1, the result is B5/C5
  • When B5/C5 is > 1, the result is 1 (100%)
  • When B5/C5 = 1, the result is 1 (100%)

In the example shown, you can see that E13 and E14 are "capped" at 100%.

You can use the MAX function in a similar way to guarantee a minimum value.

With IF

For reference, the equivalent formula with the IF function looks like this:

=IF(B5/C5>1,1,B5/C5)

If the result of B5/C5 is greater than 1, return 1. Otherwise, return B5/C5. This formula works fine but is more complex and redundant.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.