• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Tiered cumulative commission by unit

T

Theprofessorlg

New Member
Please help me, I have been working on this for a while and cannot figure it out. This is what I need. My company is doing a tiered cumulative commission by unit.

Units sold 1-3 commission is $50 per
Units sold 4-6 commission is $75 per
Units sold 7+ commission is $100 per

In January a customer buys one unit and the commission is $50. In February the same customer buys 3 units. The first two units would be $100 ($50 each), but the commission for the third unit would be $75 ($75 each). The total commission for that month would be $175. In March the customer buys 3 more units. The commission on the first two units would be $150 ($75 each), and the third unit be $100. The total commission for March would be $250.

Thank you to anyone that can help!!
 

Attachments

  • Tiered cumulative commission by unit.xlsx
    37.7 KB · Views: 11
Hi:

Using Power Query, it is easily achievable, find the attached.


Thanks
 

Attachments

  • Tiered cumulative commission by unit.xlsx
    255.1 KB · Views: 6
To use a formula, first build a table of cumulative commission versus cumulative campaigns (either by formula or mental arithmetic). Then use INDEX to look up the cumulative commission at the end of each month. Subtracting the value at the beginning of the month from that achieved by the end of the month gives the commission earnt for the month.
 
Last edited:
You must log in or register to reply here.
Back
Top