Cap percentage at 100
To cap a calculated percentage at 100%, you can use the MIN function. In the example shown, the formula in D6 is:
which guarantees the result will never exceed 100%.
How this formula works
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.
For reference, the equivalent formula with the IF function looks like this:
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.