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. 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: 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: 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: 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: The second IF caps larger values at 1: When we nest the first IF inside the second, we have the final formula: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
