Summary
To cap the result of a percentage-based calculation at a specific amount, you can use the MIN function. In the example shown, the formula in E5 is:
=MIN(C5*10%,1000)
The result is a 10% bonus that will never be greater than $1,000.
Generic formula
=MIN(amount*percent,cap)
Explanation
In this example, the goal is to calculate a bonus of 10% of each project's value, with a maximum bonus of $1,000. In other words, the bonus is 10% of the value in column C, but never more than $1,000.
MIN function
This formula uses the MIN function to make a decision that might otherwise be handled with the IF function. Although MIN is usually used to return the minimum value in a data set with many numbers, it also works fine for the "lesser of the two" situations.
Inside MIN, the value in C5 is multiplied by 10%, and the result is the first number given to MIN. The number 1000 is supplied as the second value. The MIN function simply returns the smaller of the two values:
- When C5*10% is < 1000, the result is C5*10%
- When C5*10% is > 1000, the result is 1000
- When C5*10% is = 1000, the result is 1000
In the worksheet shown, Alpha's value of $2,250 returns a bonus of $225, while Omega's value of $12,750 would return $1,275 at 10%, so MIN returns the cap of $1,000 instead. Beta is capped the same way. Delta's 10% comes to exactly $1,000, so the cap changes nothing.
Replacement for IF
The equivalent IF formula looks like this:
=IF(C5*10%>1000,1000,C5*10%)
This works, but the calculation C5*10% appears twice, and the formula is harder to read. To return the smaller or greater of two values, MIN and MAX can be a clever way to avoid a more complicated IF formula. To cap a percentage at 100% instead of a fixed amount, see Cap percentage at 100.
