This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I need help with a formula that will calculate the sales commissions owed by Cumulative sales target achieved.
I.e. I have 6 sales tiers each that pay a different commission %
The Sales tiers are
| Commission Criteria | Tier | Sales Target | Rate |
|---|---|---|---|
| 0 - 124,999 | 1 | 0 | 2% |
| 125,000 - 249,999 | 2 | 125,000 | 3% |
| 250,000 - 499,999 | 3 | 250,000 | 5% |
| 500,000 - 749,999 | 4 | 500,000 | 7% |
| 750,000 - 999,999 | 5 | 750,000 | 8% |
| >1,000,000 | 6 | 1,000,000 | 8% |
Actual Net Sales are
| Apr 1 2018 | Apr 2018 | May 2018 | Jun 2018 | Jul 2018 | Aug 2018 | Sep 2018 | Oct 2018 | Nov 2018 | Dec 2018 | Jan 2019 | Feb 2019 | Mar 2019 |
|---|
| Net Sales | (103,746) | (128,613) | (49,039) | 19,392 | 55,931 | 99,364 | 135,790 | 123,370 | 149,457 | 142,755 | 137,643 | 134,022 | 131,315 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| **** | **** | **** | **** | **** | **** | **** | **** | **** | **** | **** | **** | **** | |
| Cumulative Sales | (103,746) | (232,359) | (281,398) | (262,006) | (206,075) | (106,711) | 29,079 | 152,449 | 301,907 | 444,662 | 582,304 | 716,326 | 847,641 |
Commission table
| Commissions Breakdown | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Tier 1 Amt | |||||||||||||
| Tier 2 Amt | |||||||||||||
| Tier 3 Amt | |||||||||||||
| Tier 4 Amt | |||||||||||||
| Tier 5 Amt | |||||||||||||
| Tier 6 Amt | |||||||||||||
| - | - | - | - | - | - | - | - | - | - | - | - | - |
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
Hi,
In cell C2, enter this formula and copy down
=IFERROR(VLOOKUP(B2,$F$2:$I$7,4,1),0)
In cell E2, enter this formula and copy down
=C2*D2
Hope this helps.
Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Calculate scaled monthly commissions for reps as a group.
No bonuses or fines for not meeting quotas considered.
Need different rate table if individual rep records are to be considered.
Removed rate discontinuities for simplicity.
No attempt at running totals.
http://www.mediafire.com/file/52nx2dmvrm9hmne/10_27_18.xlsx/file
http://www.mediafire.com/file/c9uh0zl5cf9iar6/10_27_18.pdf/file
I can not get this to work! Also has external data links.
Sorry, this was not what I was asking for.
Thanks, thou
Herbert
Ahish,
The negative numbers represent net sales, as some equipment gets removed from clients. The commissions are calculated on the cumulative (net) sales and once they are favorable.
Regards,
Hi,
In the Sales row, why are there both positive and negative numbers? What do they mean? How will commission be calculated on the negative numbers.