"Due to contraints, a vlookup will not work
Last problem -whenever the subtotal is different when I am multiplying by 0.05 ( in my first if statement),the calculation is wrong for max discount, EXCEPT WHEN MY SECOND IF STATEMENT IS CALCULATED THE MAX DISCOUNT IS CORRECT
=IF(OR(E9="A",E9="B",E9="CB",E9="K",E9="L",E9="M",E9="N",E9="Q",E9="R",E9="W",E9="Y"),(I9*0.05),0) +
IF(OR(E9="MD",E9="WG"),(I9*0.03),0) +
IF(AND(F2<401,E9="D"),50,IF(AND(F2>400,F5<501,E9="D"),100,IF(AND(F2>500,F5<1001,E9="D"),150,IF(AND(F2>1000,E9="D"),300,0))))+
IF(AND(F2<401,E9="H"),50,IF(AND(F2>400,F5<501,E9="H"),100,IF(AND(F2>500,F5<1001,E9="H"),150,IF(AND(F2>1000,E9="H"),300,0)))) +
IF(AND(F2<401,E9="V"),50,IF(AND(F2>400,F5<501,E9="V"),100,IF(AND(F2>500,F5<1001,E9="V"),150,IF(AND(F2>1000,E9="V"),300,0)))) +
IF(AND(F2<401,E9="T"),50,IF(AND(F2>400,F5<501,E9="T"),100,IF(AND(F2>500,F5<1001,E9="T"),150,IF(AND(F2>1000,E9="T"),300,0)))) +
IF(AND(F2<401,E9="X"),50,IF(AND(F2>400,F5<501,E9="X"),100,IF(AND(F2>500,F5<1001,E9="X"),150,IF(AND(F2>1000,E9="X"),300,0))))
Company | aaaaa | bbbb | ccccc | Subtotal | Max Discount |
CB | $ 1.00 | 10% | $ 10.00 | $ 560.00 | $ 28.50 |
L | $ 1.00 | 10% | $ 10.00 | $ 560.00 | $ 28.50 |
N | $ 1.00 | 10% | $ 10.00 | $ 560.00 | $ 28.50 |
A | $ 1.00 | 12% | $ 10.00 | $ 570.00 | $ 28.50 |
<colgroup><col><col><col><col span="2"><col></colgroup><tbody>
</tbody>