+ Reply to Thread
Results 1 to 8 of 8

Commission Calculation

  1. #1
    nospaminlich
    Guest

    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

  2. #2

    Re: 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

  3. #3
    Richard Buttrey
    Guest

    Re: Commission Calculation

    On Mon, 31 Oct 2005 07:12:10 -0800, nospaminlich
    <[email protected]> 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,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
    __________________________

  4. #4
    Ron Rosenfeld
    Guest

    Re: Commission Calculation

    On Mon, 31 Oct 2005 07:12:10 -0800, nospaminlich
    <[email protected]> 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

  5. #5
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    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.
    Bruce
    The older I get, the better I used to be.
    USA

  6. #6
    nospaminlich
    Guest

    Re: 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

  7. #7

    Re: 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

  8. #8
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Quote Originally Posted by [email protected]
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1