Hi

I'm trying to come up with a neat formula to calculate renewal commission
where commission is paid at 2% on the 4 previous years sales as long as sales
in the previous year were min £30k. If not previous years renewal commission
is lost.

What I am trying to achieve is this:

Sales Comm
Year 1 50000 0 because no sales in Year 0
Year 2 60000 1000 2% of Year 1 sales
Year 3 25000 2200 2% of Year 1 & 2 sales
Year 4 40000 0 because Year 3 sales <£30k
Year 5 60000 800 2% of Year 4 sales


I thought I could do this with sumproduct but in Year 5 it didn't exclude
the commission on years 1 and 2 which are forfeited by not achieving £30k in
year 3.

Any help would be much appreciated.

Thanks a lot