Calculate cap percentage at specific amount in Excel

by

This tutorial shows how to calculate a percentage based value capped at a specific amount using MIN function. See illustration below:

Formula

=MIN(A1*percent,1000)

Explanation

In the example shown, the formula in D6 is:

=MIN(C6*10%,1000)

How this formula works

This formula takes advantage of the fact that the MIN function works fine with small sets of data, even two values.

Worked Example:   How to get relative row numbers in a range in Excel

Inside MIN, the value in C6 is multiplied by 10%, and the result appears at the first number given to MIN. The number 1000 is supplied as the second value.

Worked Example:   Force negative numbers to zero in Excel

The MIN function then returns the smaller of the two values.

Worked Example:   Maximum value if in Excel

Leave a Reply

Your email address will not be published.