Exceljet

Quick, clean, and to the point

Cap percentage between 0 and 100

Excel formula: Cap percentage between 0 and 100
Generic formula 
=MAX(0,MIN(A1,1))
Summary 

To limit a percentage value so that it falls between 0% and 100% you can use a formula based on the MIN and MAX functions. In the example shown, the formula in C5, copied down, is:

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

The result is that negative values are forced to zero, values over 1 are capped at 1, and values between 0 and 1 are unaffected.

Note: all values formatted with percentage number format.

Explanation 

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.