How to Calculate the total commission value from a sliding scale
Category: [Excel] Demo Available
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to match positive and negative values within subgroups |
| How to sum up values in a date range |
| How to Lock/unlock cells based on value in a drop down list |
| How to sum up columns in each row and highlight until that value |
| How to use RANK function ignoring only zero values |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.