|
Commission Calculation
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 |
Commission Calculation
I am sure someone will post a neat formula based answer
however a clunky brute force UDF if it is of any interest Function myComm(myRange As Range, Cutoff As Double, rate As Double) With myRange For n = .Cells.Count To 1 Step -1 If .Cells(n).Value < Cutoff Then Exit For Else myComm = myComm + .Cells(n).Value End If Next n End With myComm = myComm * rate End Function hth RES |
Commission Calculation
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 With your data in A6:B10 the following in C6:C10 seems to work. =IF(AND(MIN(B2:B5)<30000,B530000),B5,SUMPRODUCT(( B2:B5)*(MIN(B2:B5)30000)))*0.02 There may, probably is, a more elegant solution. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Commission Calculation
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 |
Commission Calculation
There may be a prettier way to do this, but this seems to work for me: =IF(MIN(I3:I6)30000,SUM(I3:I6)*0.02,IF(MIN(I4:I6) 30000,SUM(I4:I6)*0.02,IF(MIN(I5:I6)30000,SUM(I5: I6)*0.02,IF(I630000,I6*0.02,0)))) I am assuming your annual sales are in column I (adjust this as needed). This formula can be used in years 5 and beyond--just copy down. Use similar formulas for years 2-4 as such: Year 4:=IF(MIN(I3:I5)30000,SUM(I3:I5)*0.02,IF(MIN(I3:I 5)30000,SUM(I3:I5)*0.02,IF(I530000,I5*0.02,0))) Year 3:=IF(MIN(I3:I4)30000,SUM(I3:I4)*0.02,IF(I430000 ,I4*0.02,0)) Year 2:=IF(I330000,I3*0.02,0) Does this work for you? -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=480617 |
Commission Calculation
If none of the other solutions work for you, try this: assumes Sales Yr. 1 is in B2 For year 2: =IF(B2=30000,B2*0.02,0) For year 3: =IF(MIN(B2:B3)=30000,SUM(B2:B3)*0.02,IF(B3=30000 ,B3*0.02,0)) For year 4: =IF(MIN(B2:B4)=30000,SUM(B2:B4)*0.02,IF(MIN(B2:B4 )=30000,SUM(B2:B4)*0.02,IF(B4=30000,B4*0.02,0)) ) For years 5+: =IF(MIN(B2:B5)=30000,SUM(B2:B5)*0.02,IF(MIN(B3:B5 )=30000,SUM(B3:B5)*0.02,IF(MIN(B4:B5)=30000,SUM( B4:B5)*0.02,IF(B5=30000,B5*0.02,0)))) then copy this last cell down as far as needed. The basics of this is to look at the 4 prior years. If none are below 30000, sum and multiply by .02. If one value is low, look at 3 prior years and repeat logic, repeat same for 2 prior years, and finally look at last year. If this fails, return zero. Does this work for you? The UDF works from years 4 and onward. The SUMPRODUCT seems to fail when the <30000 is 2,3 or 4 years prior. -- swatsp0p ------------------------------------------------------------------------ swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101 View this thread: http://www.excelforum.com/showthread...hreadid=480617 |
Commission Calculation
Thanks a lot to everyone who's helped.
It's been educational comparing the different solutions and working out the logic behind them. Thanks again |
Commission Calculation
The UDF works from years 4 and onward.
I have put a check line in to stop the loop if you get to a non numeric entry However I had envisaged that the range was entered to only include the possible data ie. in year 2 the range only included one cell for year 1. Function myComm(myRange As Range, Cutoff As Double, rate As Double) With myRange For n = .Cells.Count To 1 Step -1 If .Cells(n).Value < Cutoff _ Or Not (IsNumeric(.Cells(n))) Then Exit For Else myComm = myComm + .Cells(n).Value End If Next n End With myComm = myComm * rate End Function cheers RES |
| All times are GMT +1. The time now is 04:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com