Summary

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

=MIN(1,B5/C5)

which guarantees the result will never exceed 100%. To keep a percentage between 0% and 100%, see Cap between 0% and 100% below.

Generic formula

=MIN(1,A1/B1)

Explanation

In this example, the goal is to calculate a percentage from the points in column B and the maximum in column C, without letting the result go over 100%. Column E shows the uncapped result of B5/C5 for comparison, and it goes over 100% in rows 13 and 14.

MIN function

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 F13 and F14 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.

Cap between 0% and 100%

Sometimes a percentage can also go below zero. In the worksheet below, the goal is to limit incoming percentage values so that they fall between 0% and 100%: negative values become zero, values over 100% become 100%, and values in between are unaffected. The formula in D5, copied down, is:

=MAX(0,MIN(1,B5))

Cap percentage between 0% and 100% with MIN and MAX

This is an example of nesting: the MIN function is nested inside the MAX function. Working from the inside out, MIN caps the value at 1, as in the formula above. In the example, B5 contains -5% (-0.05), so MIN returns -0.05. This result is returned directly to the MAX function:

=MAX(0,-0.05) // returns 0

Because zero is larger than -0.05, MAX returns zero as a final result. The original value is discarded.

The IF function can also solve this problem, but it takes two IFs, one nested inside the other:

=IF(B5>1,1,IF(B5<0,0,B5))

This returns the same results as the MIN and MAX formula, but notice the reference to B5 occurs three separate times. When you need to make a choice based on smaller or larger values, the MIN and MAX functions can be a clever and elegant way to keep a formula simple.

If B5 is empty, MIN ignores it, and the formula returns 100%. To treat an empty cell as zero, add zero to the value first: =MAX(0,MIN(1,B5+0)). This only matters when MIN gets the cell directly; in =MIN(1,B5/C5) above, the division turns an empty B5 into zero.

OK, now that we've talked about nesting, I should mention that it is possible to solve this problem without any nesting at all with the MEDIAN function, as shown in column E:

=MEDIAN(0,1,B5)

This works because the MEDIAN function returns the median (middle number) in a group of numbers. When a value is negative, zero becomes the middle number. When a number is greater than 1, 1 becomes the middle number. Clever!

However, note MEDIAN only returns the middle number when the total number of values is odd. If the number of values is even, MEDIAN returns the average of the two numbers in the middle. As a consequence, if B5 is empty, MEDIAN will return the average of 1 and zero, which is 0.5, or 50% when formatted as a percentage.

Video: Simplified formula example 401k Match (4 min, free). It's one of 117 lessons in our Core Formula course.

Support Exceljet

Many websites (including Exceljet) have lost over half their traffic in the past year because of the crushing impact of AI on web searches. Exceljet is still at it, publishing free content each week. If you find Exceljet helpful, you can support the site and go ad-free with the Exceljet Lifetime Pass. One payment, no subscription, and ads are gone forever. Plus, you get our Excel Shortcuts video course as a thank you.

Get the Lifetime Pass

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.