• 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.

Help with Commission In BetweenTier

Ang Ang

New Member
Hi Excel Expert,

Just want to know if anyone can help me with a formula for how to calculate commission rate. I have attached a sample excel file.

For example:

If the the sale = 1,320,000 the commission rate = .209% and if the sale is = 2,111,999 the rate = .264%. I don't know how to get a rate for sale that is in between.

Need to find out the rate if the sales = 1,500,000 then the rate = ??? %

Commission rate Starting Quota Ending Quota
.209% to .264% $ 1,320,000 $ 2,111,999

Thanks
 

Attachments

  • Rate Tier.xlsx
    14.1 KB · Views: 8
The solution for your example is:
=B3+(C3-B3)*((1500000-D3)/(E3-D3))
=0.222%

For a generic solution:
=OFFSET(B1,MATCH(G2,D2:D9,1),0)+(OFFSET(C1,MATCH(G2,D2:D9,1),0)-OFFSET(B1,MATCH(G2,D2:D9,1),0))*((G2-OFFSET(D1,MATCH(G2,D2:D9,1),0))/(OFFSET(E1,MATCH(G2,D2:D9,1),0)-OFFSET(D1,MATCH(G2,D2:D9,1),0)))
where G2: has the value 1,500,000
 
Perhaps something like this?
 

Attachments

  • Rate Tier.xlsx
    14.6 KB · Views: 9
Last edited:
Back
Top