Calculate commision/royalty in excel

[Celeste3252]

I have a similar question to this, but the customer needs to pay the royalty percentage on each sales tier, so if they sold 15 million, they would pay 10% royalty on the first 10 million and 9% on the second 5 million. I created a lookup like the one above but used revenue for A2 also. I can not figure out how to adjust the formula. Any help would be appreciated.

=LOOKUP(E22,{0,10000001,20000001,30000001,40000001;10,9,8,7,5})%*E22

Royalty Tier 1 0 - $10,000,000 10%
Royalty Tier 2 $10,000,001 - $20,000,000 9%
Royalty Tier 3 $20,000,001 - $30,000,000 8%
Royalty Tier 4 $30,000,001 - $40,000,000 7%
Royalty Tier 5 40,000,000 and over 5%

[JBeaucaire]

Commission sample
http://www.excelforum.com/attachment...ple-tiers-.xls
Here's a sample file that shows you an example of TIERED discounting structure:

Complex vs Simple discount techniques

[Celeste3252]

I think I already thanked you but do not see the message. Thank you so much. I was able to fix my formula and now know how to use SUMProduct.

[Vikas_Gautam]

Try this..

Please Login or Register  to view this content.

However you can also use the ranges instead of arrays.