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

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(A15,B2+INT(ROUNDUP((A1/0.5),0))*B3,
INT(ROUNDUP((A1/0.5),0))*B1)

--
Regards

Roger Govier


"Mark Toulson" wrote in message
...
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

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

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

"Roger Govier" wrote in message
...
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(A15,B2+INT(ROUNDUP((A1/0.5),0))*B3,
INT(ROUNDUP((A1/0.5),0))*B1)

--
Regards

Roger Govier


"Mark Toulson" wrote in message
...
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

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.

--
Regards

Roger Govier


"Bernard Liengme" wrote in message
...
Expanding on Roger's answer:
If the special rate (b3) applies only to the weight in excess of 5 kg
then
=IF(A15,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)+(A15)*(B2+ROUNDUP(((A1-5)/0.5),0)*B3)

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

"Roger Govier" wrote in message
...
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(A15,B2+INT(ROUNDUP((A1/0.5),0))*B3,
INT(ROUNDUP((A1/0.5),0))*B1)

--
Regards

Roger Govier


"Mark Toulson" wrote in
message ...
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