Calculating the total commission value from a sliding scale

  • I am trying to figure out how to work out the total commission payble to an agent when you enter a purchase price. The commission is calculated on a sliding scale as follow for example:


    For the first 100 000, the commission the agent gets is 5% of the purchase price, for the next 100 000, thus from 100 001 - 200 000, he gets an additional 4.5%, then for 200 001 - 300 000, he gets and additional 4%, for 300 001 - 400 000, its 3.5% then anything over 401 000 he gets 3%.


    Thus say Purchase price is 210 000 - he gets 5 000 for 1st 100 000, 4500 for next 100 000 and R400 for the balance, so total commission on R210 000 is 9800.


    Now just to put it in a formula that we only need to enter the purchase price and the formula works out the commission per sliding scale.

  • Re: Calculating the total commission value from a sliding scale


    Try this, change the A1 to suit the actual cell address where the sale value is.


    =IF(A1<=100000,A1*0.05,IF(A1<=200000,5000+((A1-100000)*0.045),IF(A1<=300000,9500+((A1-200000)*0.04),IF(A1<=400000,13500+((A1-300000)*0.035),13850+((A1-400000)*0.03)))))


    Note that in the sample you gave the total commission should be (5000 + 4500 +400) which is 9900 not 9800.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Calculating the total commission value from a sliding scale


    Thank youKjBox, yes you correct, must be 9900, blue monday :-)


    The formula giving error, but understand your logic in the formula, going to play around a bit - did try to add sum in but still giving error. Will let you know once it works. Thank you so far!

  • Re: Calculating the total commission value from a sliding scale


    I do not get an error with it. What cell is your Sales Value in, and in which cell do you want the commission due to be calculated?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Calculating the total commission value from a sliding scale


    I have placed the sales value in A1 (to test the formula before i change anything) - The formula totally makes sense but excel give me error that they found a problem with the formula

  • Re: Calculating the total commission value from a sliding scale


    No idea why you should get a error, unless you missed something whilst copy/pasting the formula.


    Here is a file with it working.

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Calculating the total commission value from a sliding scale


    thank you KjBox, the one you attached work perfectly. I pasted then retyped the formula as i thought maybe something woring with the pasting but also gave error. But yours work perfectly. Thank you again.

  • Re: Calculating the total commission value from a sliding scale


    should the formula be
    [COLOR="#0000FF"]=IF(A1<=100000,A1*0.05,IF(A1<=200000,5000+((A1-100000)*0.045),IF(A1<=300000,9500+((A1-200000)*0.04),IF(A1<=400000,13500+((A1-300000)*0.035),[/COLOR][COLOR="#FF0000"]17000[/COLOR][COLOR="#0000FF"][/COLOR][COLOR="#0000FF"]+((A1-400000)*0.03)))))[/COLOR]

  • Re: Calculating the total commission value from a sliding scale


    Thanks Pike, you are right, I added 350 to 13500 instead of adding 3500 :)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.