Explanation
to make it understand this problem, see to it you understand howpercentage number formatting works.
The number 1, when formatted as a percentage, is 100%.More on number formats here.
Nesting is a key building block for more advanced formulas.
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 theMAX 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, theIF functioncan also be used to solve this problem.
To do this, we need two separate IF functions.
Notice, for example, the reference to B5 occurs three separate times.
When a value is negative, zero becomes the middle number.
When a number is greater than 1, 1 becomes the middle number.
However, note MEDIAN only returns the middle number when the total number of values isodd.
If the number of values iseven, MEDIAN returns theaverageof the two numbers in the middle.
The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.
MAX Function
The Excel MAX function returns the largest numeric value in the data provided.
MAX ignores empty cells, the logical values TRUE and FALSE, and text values.
The Excel MEDIAN function returns the median (middle number) in the supplied set of data.
For example, =MEDIAN(1,2,3,4,5) returns 3.
More than one condition can be tested by nesting IF functions.
