 # 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

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!