Notice that the first argument pair tests to see if the value in cell A2 is less than the value in E2, which is $250.
If this evaluates to true, the formula should return a value of ‘0’.
This is because unless the sales value is at least $250, there is no bonus paid out.
The next step of the formula tests to see if the value in A2 is less than the value shown in E3, which is $500.
If it is less than $500 but it did not test true as less than $250, the formula is designed to return the value in cell F2, which is $25.
This simply means that if the sales amount is greater than $250 but less than $500, the bonus should be $25.
The next few argument pairs work in the same fashion until we get to the final pair.
If our sales amount has not evaluated true for all argument pairs up to this point, that leaves us with one last test.
Since $1,250 this is our last quota value to test against, we do not have a subsequent value to evaluate the sales amount as being less than.
Therefore, if the sales amount has not been less than any of the quota amounts up to the value of $1,250 in cell E5, it must be greater than that value and our task is to tell our ‘IFS’ function how to evaluate that.
Now we simply ask the formula to test if the sales amount value is greater than E6 and to return the bonus value in F6.
See in the next figure that both the ‘IF’ and ‘IFS’ formulas evaluate to the same results.
Rounding up on nesting…
Obviously, the ‘IFS’ function provides a much clearer and more efficient alternative to nested ‘IF’ functions.