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.

    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


    You're welcome

    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


    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login