How do I create a formula that calculates shipping costs?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mark Toulson - 16 Oct 2006 14:58 GMT

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

Reply to this Message

Roger Govier - 16 Oct 2006 16:16 GMT

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)

Signature

Regards

Roger Govier

>I would kike to calculate shipping costs before I receive the invoice
>from
[quoted text clipped - 11 lines]
>
> Thanks

Reply to this Message

Bernard Liengme - 16 Oct 2006 20:06 GMT

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

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Hi Mark
>
[quoted text clipped - 18 lines]
>>
>> Thanks

Reply to this Message

Roger Govier - 16 Oct 2006 22:40 GMT

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.

Signature

Regards

Roger Govier

> Expanding on Roger's answer:
> If the special rate (b3) applies only to the weight in excess of 5 kg
[quoted text clipped - 30 lines]
>>>
>>> Thanks

Reply to this Message