On Mon, 31 Oct 2005 07:12:10 -0800, nospaminlich
wrote:

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

Try this:

Assumptions:

B2:Bn == Sales
C2:Cn == Commissions

Enter:

C2: 0
C3: =IF(B2=30000,B2*0.02+C2,0)
C4: =IF(B2=30000,B2*0.02+C2,0)
C5: =IF(B4=30000,B4*0.02+C4,0)
C6: =IF(B5=30000,MIN(SUM(B2:B5)*0.02,B5*0.02+C5),0)

copy/drag C6 down as far as necessary.



--ron