multiple % discount dependent on sales value

I am trying to create a formula for calculating the value of discount applicalbe in repec of a running total of sales to date ie.

anything below £40k is subject to no discount (0%)
between £40k and £120k, then discount is only applicable on the sales over £40k at a rate of 6%
over £120k, then discount is applicable at 6% on the sales between £40k and £120k and then at 8% on anything over £120k -

make sense?
Please can anyone help, I'm struglling to get a IF solution to I can crack the 0% and 6% idea, but the 3rd rule of 8% sends it all crazy.

Many thanks

Originally Posted by starguy

Try this

=IF(A1<40,"",IF(AND(A1>40,A1<120),(A1-40)*0.06,IF(A1>=120,(3.6+(A1-120)*0.08))))

Starguy,

Thanks for this, but should there be another AND for the 3rd IF statement - if the sales top £120k, the formula results in a discount figure which isn't corect.
What I'm after is the sum of the discounts 0% to 40k, 6% between 40k and 120k, plus the 8% at over 120k

so 130k sales would be 0-40k = 0%, 40-120k = 75.48k, >120k = 9.26k

total discount is (80-75.48)+(10-9.26) = 5.26k

don't forget it's a discount! so we need the deducted sum!

so what I have now is

=IF(A1<40,"",IF(AND(A1>40,A1<120),(A1-40)/1.06,IF(A1>=120,(3.6+(A1-120)/1.08)))),

but I now need find the value to deduct this from, which is A1-40 ?!?!?



Cheers, I think