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 f...
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.
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
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
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
Could you be so kind to answer the question that I asked?
- The answer of Q1: When they sell up to 250 units above target,
The answer of Q2: Under the Commission Calculations section there is a column (sold amount), the percentage will be applied to that.Thank you. So the sales rep gets 5.5% commission if they sell between 1000 and 1500 units above target.
But what if they sell more than 1500 units above target? Does it remain 5.5%?