Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Step Based Commission Formula

  1. #1
    Join Date
    7th September 2006
    Posts
    8

    Step Based Commission Formula

    I am having trouble with the code for this stepped scale commission structure.

    Net Service
    $1,1400

    Step Scale Comm. %
    $0-500 40%
    $500-750 45%
    $750-1000 47%
    $1000-9999.99 50%

    Final Commission Paid $ ?
    Last edited by saint3232; September 7th, 2006 at 12:38.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th September 2003
    Posts
    401

    Re: step based commission formula

    Are you looking for a formula?
    if so
    =IF(A1<500,A1*0.4,IF(A1<750,A1*0.45,IF(A1<1000,A1*0.47,IF(A1<=9999.99,A1*0.5,"Over $10,000"))))

    Ross

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    7th September 2006
    Posts
    8

    Re: step based commission formula

    Hey Ross,

    Thanks a bunch. However I think there is supposed to be a another piece to the code. I am not sure I explained myself well. For example;

    If a service provider makes $1500 for the week and they are on a stepped scale then it would break out accordingly.

    on the first $500 They would be paid 40%
    on the $500 up to $750 they would paid 45% (on that $250)
    on the $750 up to $1000 they would be paid 47% (on that $250)
    on the remaining amount above $1000 up to $9999.99 they would be paid at 50%.

    I am including a line of code for a three tier commission structure so you can see what I am talking about. The only difference between the two types of tiers is the extra tier (47%)

    =IF(C7<=1000,C7*0.45,IF(AND(C7>1000,C7<=1500),(450+(C7-1000)*0.5),IF(AND(C7>1500,C7<10000),450+250+(C7-1500)*0.6,"oops")))

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: step based commission formula

    Hi,

    =IF(C7<10000,IF((C7>1000)*(C7<10000),430+(C7-1000)*0.5,LOOKUP(C7,{0,500,750},{200,312.5,430})),"oops")

    HTH

  5. #5
    Join Date
    7th September 2006
    Posts
    8

    Re: step based commission formula

    Hi HTH,

    Thanks for the post. However that's not exactly what I am looking for. When I inserted the formula you supplied the answer did not come out right.
    Here is what I have so far. I know that I am missing something simple. Could you take a look to see if you can figure out where I am going wrong?

    =IF(C25<=500,C25*0.40,IF(AND(C25>500,C25<=750),(200+(C25-750)*0.45),IF(AND(C25>750,C25<=1000),200+113+(C25-1000)*0.47),IF(AND(C25>1000,C25<10000),200+113+113(C25-10000)*0.5,"oops"))))

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: step based commission formula

    Hi,

    Sorry! See this revised one.

    =IF(C7<10000,40%*MIN(C7,500)+45%*MAX(MIN(C7-500,250),0)+47%*MAX(MIN(C7-750,250),0)+50%*MAX(C7-1000,0),"oops")

    HTH

    BTW, HTH=Hope This Helps!

  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,798

  8. #8
    Join Date
    7th September 2006
    Posts
    8

    Re: Step Based Commission Formula

    It worked! Thank you very much. I appreciate your continued effort.
    Thanks also for the "HTH" lingo. I am still a bit of a novice in this forum.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Step Based Calculation Formula
    By isomorph in forum Excel General
    Replies: 1
    Last Post: October 23rd, 2007, 17:42
  2. Calculate Rate/Commission Based on Many Criteria
    By ScottyAPS in forum Excel General
    Replies: 6
    Last Post: September 30th, 2006, 16:56
  3. Zero Based Commission Formula
    By saint3232 in forum Excel General
    Replies: 6
    Last Post: September 21st, 2006, 21:58
  4. Sliding Commission Formula
    By duq in forum Excel General
    Replies: 3
    Last Post: September 16th, 2005, 17:45
  5. vba commission formula
    By stevengb in forum Excel and/or Access Help
    Replies: 8
    Last Post: July 8th, 2005, 18:45

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
  •  
porno