How to Calculate the total commission value from a sliding scale

Requirement:

The user 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.

Solution:

=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)))))

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.