Formula

[Val-09]

 Hi guys,

Your help will be very much appreciated:

write a formula that will automatically calculate total price for any amount of units placed in C12.

It will not allow me to post my screenshot for some reason: So I will explain best I can.

B5 (unit quantity) C5 (Price per unit)

B6 Below 5           C6 36

B7 5-10                C7 34

B8 11-30              C8 31

B9 31-50              C9 26

B10 51 above      C10 20

B12 # of units

B13 Total price (write formula here)

[#2]

@Val-09 Because of the way you have the table set up you make it difficult.  For example all your quantities are text based ranges and you "Below 5" is exclusive range while all your other ranges are inclusive ranges (i.e. the range includes that number).  If the table was set up as

QTY <=     Price

4                36

10              34

30              31

50              26

more         20

then you could use a simple lookup function like XLOOKUP 

=C12*XLOOKUP(C12,B6:B10,C6:C10,,1,1 )

which means if you change the numbers in the table they automatically adjust.

If you can't or don't want to change the table format then in C13 you can force a formula like this:

=C12*IFS(C12<5,C6,C12<=10,C7,C12<=30,C8,C12<=50,C9,TRUE,C10)

[#3]

Thank you so much for your help! @mtarler