multi tiered percentage rent calculation

[CoreyWA]

I need to calculate percentage rent owed when their are multiple percentage
rent rates with different breakpoints. The formula below returns "False" if
N20=0.

=IF($N$20>0,IF(I13>$M$20,(I13-$M$20)*$N$20+($M$20-$M$19)*$N$19+($M$19-$M$18)*$N$18,IF($N$19>0,IF(I13>$M$19,(I13-$M$19)*$N$19+($M$19-$M$18)*$N$18,IF($N$18>0,IF(I13>$M$18,(I13-$M$18)*$N$18,0))))))

Where N20 = % Rate 3
N19 = % Rate 2
N18 = % Rate 1
M20 = Breakpoint 3
M19 = Breakpoint 2
M18 = breakpoint 1
I 13 = sales

 

[Fred Smith]

And what would you like it to return?

Regards,
Fred.

 

[CoreyWA]

The formula should show the percentage rent owed (1) if a percentage rate
exists and (2) sales exceed each breakpoint that corresponds to the
percentage rate or zero if all values in the percentage rate (column N18 thru
N20) are 0 or if sales do not exceed the breakpoint for all levels M18 thru
M20.

If N 20 (third tier % rate) has a number greater than zero it should check
if sales (I13) exceed breakpoint (M20) if sales exceed M20 then it should
calculate the percentage rent owed for each of the three levels
((I13-M20)*n20) + ((m20-m19)*n19) + ((m19-m18)*n18) if sales do not exceed
M20, or if the amount in the third tier (N20) is less than 1 then it should
look if percent rate 2 (N19) >0, if greater than zero it should determine if
sales (I13) > M19) exceeds the second breakpoint and calculate the percentage
rent owed for breakpoints two and one (M19 and M18) if N19 is =0 or blank it
should check if N18 >0 or blank, if N18>0 the formula should look to see if
sales (I13>M18) if yes then calculate percentage rent owed for tier 1, if no
then return 0.

Thanks for your help
Corey

 

[Fred Smith]

You are getting False when N20 is zero because you haven't told Excel what
to do when N20<=0. When there is nothing in the "do if false" part of an If
statement, Excel simply returns False. To address this issue, change your If
statement along the following lines:

=IF($N$20>0,IF(I13>$M$20,(I13-$M$20)*$N$20+($M$20-$M$19)*$N$19+($M$19-$M$18)*$N$18,IF($N$19>0,IF(I13>$M$19,(I13-$M$19)*$N$19+($M$19-$M$18)*$N$18,IF($N$18>0,IF(I13>$M$18,(I13-$M$18)*$N$18,0))))),"Put
here what happens when N20=0")

Regards,
Fred.