Forum Discussion
Formula
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 Replies
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)
