Require formula to look at multiple variables to give a rebate amount based on % turnover

Hi all,

I'm normally pretty good with excel formulas but this one has me a little stumped and I fear I'm entering territory I'm unfamiliar with!

In essence I need to look at the %age spend over a target to give a total rebate. The rebate %ages are variable (as we're not sure how to pitch them just yet so will need to alter them up or down). I need the formula to look at the %over spend and decide the following;

Anything up to 3% gives no rebate (band 1)
Anything over 3% gives a 3% rebate on the extra over figure, up to the next band (10%)
Anything over 10% give the full amount of the 3% rebate, plus anything over this then at 10%
(and so-on up to the last band where anything over this is at the last band rate).

I thought about putting formulas for each band into different columns then hiding them and adding them up but am sure there is a solution out there which someone would be kind enough to explain to me.

Thanks in advance

Mark.

Rebate Models - Help!.xlsx

Hi there, Fotis. Thanks for the reply!

I've been playing about with this on and off since Monday but the formula you suggested has fixed points in for the tax breaks. I need the variables to be changable until we decide what level of discout to apply to the spend over targeted turnover....

Any other ideas?

Kind regards

Mark.