How do I create a formula that calculates shipping costs?

[Guest]

I would kike to calculate shipping costs before I receive the invoice from
the supplier. The costs are based on destination and weight of the shipment.
For example, I may have one shipment to Austria weighing 2.5 kilos, and
another weighing 25 kilos. The rates are calculated in increments of half
kilos up to 5 kilos, then after that I have a basic charge of 5 kilos plus a
reduced 0.5 kilo rate.

Can anyone help?

Thanks

 

[Roger Govier]

Hi Mark

With Weight of Item in A1
Price per standard 0.5 kilo in B1
5 Kilo price in B2
Reduced 0.5 kilo price in B3

=IF(A1>5,B2+INT(ROUNDUP((A1/0.5),0))*B3,
INT(ROUNDUP((A1/0.5),0))*B1)

 

[Bernard Liengme]

Expanding on Roger's answer:
If the special rate (b3) applies only to the weight in excess of 5 kg then
=IF(A1>5,B2+INT(ROUNDUP(((A1-5)/0.5),0))*B3,INT(ROUNDUP((A1/0.5),0))*B1)

And if you want to avoid IF
=(A1<=5)*(ROUNDUP((A1/0.5),0)*B1)+(A1>5)*(B2+ROUNDUP(((A1-5)/0.5),0)*B3)

best wishes

 

[Roger Govier]

Good point Bernard.
I hadn't thought about the case where the reduced rate only applied to
weight above 5.0 kilos.
Also, i quite agree about leaving out the If's - much nicer.