Help with Commission In BetweenTier

[Ang Ang]

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

 

[Hui]

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

 

[azumi]

Perhaps something like this?

 

[Ang Ang]

Thank you!