MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formulas using


Posted by David Sears on February 10, 2002 12:56 PM

Mr, Excel,

I am trying to write a formula that I think should be very easy for you but I am lost.
This is going to be used to figure out the cost of shipping.

If the weight is 130 or less then = min of 130, but if weight is >130 but < 250 is at .82 per kgs. If weight is > 250 but <500 is at .66 per kgs. If weight is >500 .55 per kgs.

Thank you for any support or advice you can give me.

David Sears


Posted by Aladin Akyurek on February 10, 2002 1:12 PM

What is "min of 130"?

I call it X whatever it might be in the formula that follows:


=A1* ((A1 <= 130) * X + (A1 > 130) * (A1 <= 250) * .82 + (A1 > 250) * (A1 <= 500) * .66 + (A1 > 500) * .55)

Replace X by the real value.

========

Posted by IML on February 10, 2002 7:51 PM

Or..

=((A1 > 130)*A1*VLOOKUP(A1,{0,0.82;250,0.66;500,0.55},2))+((A1 < = 130)*A1*x)