Payable percentage based on Product type an amount charged

[latarshac]

Is there a way to calculate a percentage based on these conditions
I need to figure a formula to calculate if Product A is sold and the cost is between 199-208, to pay the commission out at 7.5% of this cost.. so if it was sold for 205 then at 7.5% of this cost the amount that is payable as commission would be $15.38, if it was sold for 219 then at 11% of this cost is payable as commission would be $24.09.. etc

To make this even more complicated, I would like the formula to also determine if the customer status is Active (not cancelled) and the comm status is not Expired.

The old way to pay this out was just a flat rate based on the product, if they were Active and if they were not Expired then:

'=IF(OR(I20="NOT-ACTIVE",G20="EXPIRED",G20="REMOVED"),0,IF(OR(D20="PRODUCTA",D20="PRODUCTB"),30,IF(OR(D20="PRODUCTE",D20="PRODUCTH",D20="PRODUCTD"),15,IF(D20="PRODUCTF",20,IF(D20="PRODUCTG",10,)))))

COLUMN D COLUMN G COLUMN I COLUMN Q
PRODTYPE-D20 COMMSTAT-G20 CUSTSTAT-I20 RENTINCOME-Q20
producta expired active 205
productb not-expired active 260
productd not-expired active 115


This is my list of of possible combinations.
If Product A
cost is 239 payable percentage is 15% of cost
cost is 229 payable percentage is 13% of cost
cost is 219 payable percentage is 11% of cost
cost is 209 payable percentage is 10% of cost
cost is 199 payable percentage is 7.5% of cost

If Product B
cost is 289 payable percentage is 15% of cost
cost is 279 payable percentage is 13% of cost
cost is 269 payable percentage is 11% of cost
cost is 259 payable percentage is 10% of cost
cost is 149 payable percentage is 7.5% of cost

If Product C
cost is 319 payable percentage is 15% of cost
cost is 309 payable percentage is 13% of cost
cost is 299 payable percentage is 11% of cost
cost is 289 payable percentage is 10% of cost
cost is 279 payable percentage is 7.5% of cost

If Product D
cost is 139 payable percentage is 15% of cost
cost is 129 payable percentage is 13% of cost
cost is 109 payable percentage is 11% of cost
cost is 99 payable percentage is 10% of cost

This is an example list of data

COLUMN D COLUMN G COLUMN I COLUMN Q
PRODTYPE-D20 COMMSTAT-G20 CUSTSTAT-I20 RENTINCOME-Q20
producta expired active 205
productb not-expired active 260
productd not-expired active 115

This is the formula I have been able to come up with, but it is for only 1 product type. It does work but I need to be able to put all 5 product types as possibilities.

'=IF(D20="PRODUCTB",IF(Q20<249,IF(Q20<258,SUM(Q20*7.5%),IF(Q20>=259,IF(Q20<268,SUM(Q20*10%),IF(Q20>=269,IF(Q20<278,SUM(Q20*11%),(IF(Q20>=279,IF(Q20<288,SUM(Q20*13%),IF(Q20>=288,SUM(Q20*15%,0,),0)))))))))))

COLUMN D COLUMN G COLUMN I COLUMN Q COLUMN S
PRODTYPE-D20 COMMSTAT-G20 CUSTSTAT-I20 RENTINCOME-Q20 AMTTOPAY-S20

productb not-expired active 201 $15.08