Forum Discussion
Solved
Commission calculation based on variable targets
Hello Excel Masters and Experts,
I am stuck in a formula, i need your help to solve it.
I'm attaching an excel sheet and all the information has been written on it. (cOMMISSION CAL.xlsx)
Looking forward to the solution.
Thanks in advance
OK, so the 1500 doesn't play any role in this. See the attached version. I created a small lookup table and used a VLOOKUP formula.
9 Replies
When exactly does the sales rep get 2% commission? When they sell up to 250 units above target, or when they sell at least 250 units above target?
And what (which column) is the commission percentage applied to?
- HansVogelaar,
Let say, The target of person A is 500 units, and he sold 650 units, the commission will be in excess (150 units). and commission will be calculated in the 3rd yellow highlighted column (column name in the sheet is (Comm %).
Person B, has a target of 833 units to sell, he sold 751, so no commission
another example of person B, target of person B is 833 units, he sold 945, so his commission will be on (945-833) 112 units
Target Brackets are mentioned in the sheet are below.
Commission Benchmark Commission %
Target unit + 250 surplus 2
Target unit + 500 surplus 3
Target unit + 750 surplus 4
Target unit + 1000 surplus 5
Target unit + 1500 surplus 5.5- Commission Benchmark
Target unit + 250 surplus - Commission %2
Target unit + 500 surplus - Commission %3
Target unit + 750 surplus - Commission %4
Target unit + 1000 surplus - Commission %5
Target unit + 1500 surplus - Commission %5.5
