I have a table of rates with product types and quantities.
There are 5 product types and the rate changes for each one by quantity as follows: 0 – 500, 501-1000, 1001-3000, 3000+
I can use the following formula which works OK for one product type but excel will not allow me to nest enough IF statements to satisfy the number of product types.
=IF(B2="Type1",IF(AND(C2>0,C2<=500),B10,IF(B2="Type1",IF(AND(C2>500,C2<=1000),C10,……etc
I believe I can use MATCH but can’t seem to use the <,>= to satisfy the quantities. Can anyone help?
There are 5 product types and the rate changes for each one by quantity as follows: 0 – 500, 501-1000, 1001-3000, 3000+
I can use the following formula which works OK for one product type but excel will not allow me to nest enough IF statements to satisfy the number of product types.
=IF(B2="Type1",IF(AND(C2>0,C2<=500),B10,IF(B2="Type1",IF(AND(C2>500,C2<=1000),C10,……etc
I believe I can use MATCH but can’t seem to use the <,>= to satisfy the quantities. Can anyone help?