Commission Calculation

[Guest]

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

 

[Robert_Steel]

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

 

[Richard Buttrey]

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,B5>30000),B5,SUMPRODUCT((B2:B5)*(MIN(B2:B5)>30000)))*0.02

There may, probably is, a more elegant solution.

HTH


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Ron Rosenfeld]

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

 

[swatsp0p]

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(I6>30000,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:I5)>30000,SUM(I3:I5)*0.02,IF(I5>30000,I5*0.02,0)))

Year 3:=IF(MIN(I3:I4)>30000,SUM(I3:I4)*0.02,IF(I4>30000,I4*0.02,0))
Year 2:=IF(I3>30000,I3*0.02,0)

Does this work for you?

 

[swatsp0p]

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.

 

[Guest]

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

 

[Robert_Steel]

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

 

[swatsp0p]

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.
cheers RES

Robert: you are correct, the UDF would work in that scenario. I
envisioned entering the UDF once, then copying down a range without
changing the 'myRange As Range' entry, hence my comment. Sorry for the
oversight on my part.

By the way, nice UDF! Kudos!