I am trying to create a calculation table for prices based on QTY and price break tier. I have an IF formula that doesn’t seem to be working correctly and could use some help! Below I will post a screenshot of the table, the formula I am using, and amplifying details:
Price Break formula: =IF(H3<=24,C3,IF(25<=H3<=99,D3,IF(100<=H3<=499,E3,IF(500<=H3<=999,F3,IF(1000<=H3,G3)))))*H3
Amplifying Info: Column "H" is derived from a sum of "J:P". What I want to do is automatically calculate the total quantity requested (Column "H") and based off of that value calculate the total price of each item in Column "I". IE if the total QTY=1 the price would be 1xC (Associated cell C3:C14); If the QTY=600, then it would be 600xF. Currently the formula only works if I manually input "1" as the QTY and no other numbers work. Column "H" is set as number (though general did the same thing). If i place any other number or what the calculation comes out to it puts a dash as shown in the picture.
I’m not sure if there is better way to do this with VLOOKUP or another function?
>Solution :
Try using MATCH and CHOOSE:
=CHOOSE(MATCH(H2,{0,25,100,500,1000}),C2,D2,E2,F2,G2)*H2
The MATCH will compare the qty (in H2) to the values in the array ({0,25,100,500,1000}) and return a number (1 to 5).
The CHOOSE will then take this number and choose the nth value from the list of cells (C2,D2,E2,F2,G2)
We then multiply the value in the chosen cell by the quantity in H2