which guarantees the result will never exceed 100%. 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 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
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.
