Exceljet

Quick, clean, and to the point

Cap percentage at 100

Excel formula: Cap percentage at 100
Generic formula 
=MIN(1,A1/B1)
Explanation 

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

How this formula works

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.