samedi 5 mars 2016

Excel IF function and in between values, but only if ....

Can't get it work and it is really frustrating!

I have values for postage, pricing and postage service (only if). I have two choices for postage service (express and eco), price depends on a weight, but service depends on a price (fast service for items over £5, eco - under).

Service: if product price(A2) <5=eco; >5=express

Service price(C2) by weight(B2): <=1000gr= £2 eco or £3 express 1001-1250gr= £5 eco or £6 express 1251-5000gr=£9 eco or £11 express

Cells A2 and B2 always display a value, need a formula for C2 to display the price of service calculated by weight, but if item over £5 must display express service price if less - eco.

I have tried:

IF(AND(OR(B2<=1000),A2<5),2,IF(AND(OR(B2>1000,B2<=1250),A2<5),5,IF(AND(OR(B2>1250,B2<=5000),A2<5),9)))

IF(AND(OR(B2<=1000),A2<5),2)+IF(AND(OR(B2>=1001,B2<=1250),A2<5),5)+IF(AND(OR(B2>2000),A2<5),9)

Didn't start adding A2>5, because nothing works anyway! Tried many more, but no luck. Would appreciate any help because stuck and ran out of options :( Thanks!

Aucun commentaire:

Enregistrer un commentaire