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 N180 the formula should look to see if
sales (I13M18) if yes then calculate percentage rent owed for tier 1, if no
then return 0.

Thanks for your help
Corey

"Fred Smith" wrote:

And what would you like it to return?

Regards,
Fred.

"CoreyWA" wrote in message
...
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$200,IF(I13$M$20,(I13-$M$20)*$N$20+($M$20-$M$19)*$N$19+($M$19-$M$18)*$N$18,IF($N$190,IF(I13$M$19,(I13-$M$19)*$N$19+($M$19-$M$18)*$N$18,IF($N$180,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