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))

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.