Cap percentage between 0 and 100

In order to understand this problem, make sure you understand how percentage number formatting works. In a nutshell, percentages are decimal values: 0.1 is 10%, 0.2 is 20%, and so on. The number 1, when formatted as a percentage, is 100%. More on number formats here.

The goal of this example is to limit incoming percentage values so that they fall within an upper and lower threshold. Negative values and values over 100% are not allowed, so the final result must be a number between zero and 1 (0-100%), inclusive.

Although the IF function can be used to solve this problem (see below), the result will be somewhat longer and redundant. Instead, the example shown uses a combination of the MIN and MAX functions in a very compact formula:

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

This is an example of nesting – the MIN function is nested inside the MAX function. Nesting is a key building block for more advanced formulas.

Working from the inside out, the MIN function is used to cap incoming values to 1 like this:

MIN(B5,1) // get smaller value

Translation: return the smaller of B5 and 1. For any value over 1, the value in B5 is returned. 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) // get larger value

Here, we see the formula do its work. Because zero is larger (greater) than -0.05, MAX returns zero as a final result. The original value is discarded.

IF function

As mentioned above, the IF function can also be used to solve this problem. To do this, we need two separate IF functions. One IF forces negative values to zero:

IF(B5<0,0,B5) // cap at zero

The second IF caps larger values at 1:

=IF(B5>1,1,B5) // cap at 1

When we nest the first IF inside the second, we have the final formula:

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

This is an example of a nested IF. It returns exactly the same result as the MIN and MAX formula above, but is slightly more complex and redundant. Notice, for example, the reference to B5 occurs three separate times.

The bottom line – 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.

MEDIAN Function

OK, now that we've talked about nesting, and talked about the elegance of MIN with MAX,  I should mention that it is possible to solve this problem without any nesting at all with the MEDIAN function. The generic version of the formula looks like this:

=MEDIAN(0,1,A1)

This works because 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 the target cell (A1) is empty, MEDIAN will return the average of 1 and zero, which is 0.5, or 50% when formatted as a percentage.