Cap percentage at 100

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 is 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.