LinkBack URL
About LinkBacks
I need help in setting up a formula to accurately calculate the correct commission that my company is to retain/earn on certain taxes collected. I know this will be possible to do but must admit that this formulation surpasses my level of understanding if I am to keep all of my hair its current color. I've worked with IF statements before but it has been a fairly simple true or false deal and hasn't contained this number of options with regards to the greater than/less than issue and calculating different portions of the total at different rates. For me this is difficult to wrap my head around but I have no doubt that for others, it's a fairly easy and straight forward process. Obviously, I can calculate this manually which is very simple but would rather that my spreadsheet incorporate this commission calculation automatically.
Based on the total of the taxes we have collected/billed in our system being entered on "Line 1" of the remittance slip/spreadsheet, the rules of the commission calculation are as follows:
If the amount entered on Line 1 is $3.00 or less, the commission is $3.00.
If the amount entered on Line 1 is more than $3.00 but does not exceed $20, the commission is $3.00
If the amount entered on Line 1 is more than $20 but is $3,000 or less, the commission should be calculated as a percentage of the total of Line 1 as follows: a.) 15% of the first $200, and b.) $1% of the remainder.
If the amount entered on Line 1 is more than $3,000, the commission is nil.
If anyone can help with this, I'd greatly appreciate the assistance.
Thank you.
Register To Reply
Originally Posted by esbenhaugaard
- maybe the thread-starter typed in a wrong number! At least it seams funny.
Bookmarks