Tiered cumulative commission by unit

[Theprofessorlg]

I appreciate anyone that can help me with this. 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. Please make any changes you want. The desired results is just for illustration.

Thank you to anyone that can help!!

[Glenn Kennedy]

Your data layout for your "desired results" is fairly horrible. Can I change it (radically)??

[Theprofessorlg]

your data layout for your "desired results" is fairly horrible. Can i change it (radically)??

yes please!

[Glenn Kennedy]

Can you take a moment and manually calculate the expected answers for the yellow shaded cells. Once I understand the logic, getting a formula should be (have confidence... WILL be) easy.

You had all of the cells formatted as Text. That is not much use in Excel, as (normally) you want numbers to be treated as numbers.... GENERAL is more useful, in the main.

[Theprofessorlg]

I can't tell you how much I appreciate your help! Thank you for taking the time to help me teach some things.

[Glenn Kennedy]

Here you go...


=LOOKUP(G5,$A$6:$A$8,$B$6:$B$8)*F5

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.