Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Step Based Commission Formula

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 12:38.

  • #2
    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

    Comment


    • #3
      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")))

      Comment


      • #4
        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
        Kris

        ExcelFox

        Comment


        • #5
          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"))))

          Comment


          • #6
            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!
            Kris

            ExcelFox

            Comment


            • #7
              Re: step based commission formula

              See also

              Calculate Sliding Scale Tax or Commission

              Comment


              • #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.

                Comment

                Trending

                Collapse

                There are no results that meet this criteria.

                Working...
                X