Postal formula
 

Hi,

I am trying to write a formula that automatically calculates postal
costs for items depending on their weight. However, there is one part
thats a bit more complicated.
If the item is over 1000grams the courier charges an additional £0.89
per 250g. The rest of the formula is easy (just using nested script and
<= functions) but I can't figure out how to do this in a similar manner
(ie. I don't want to have to write lots of ridiculously long nested
scripts!). grrr.

Thanks in advance for any help you can give me :)


--
omutumo
------------------------------------------------------------------------
omutumo's Profile: http://www.excelforum.com/member.php...o&userid=34681
View this thread: http://www.excelforum.com/showthread...hreadid=544474

Postal formula
 

From a prior post of mine. I have a program where you input the oz and it
calculates the postage and the proper stamps to use. I will send ONLY to
anyone who requests PRIVATELY.

Takes a while to explain but it uses INT & MOD
http://www.usps.gov/
.39 1st CLASS 1st OUNCE
.24 ADDITIIONAL OUNCE

4 Ounces
$ 1.11 TOTAL
USING ALL STAMPS
0.39 2 0.78 1.11
0.24 1 0.24 0.33
0.01 9 0.09
USING 39c & 1c ONLY
0.39 2 0.78 1.11
0.01 33 0.33
USING 24c & 1c ONLY
0.24 4 0.96 1.11
0.01 15 0.15
USING 39c FIRST & 24c & 1c
0.39 1 0.39 1.11
0.24 3 0.72 0.72
0.01 0 0.00


0.39 1st CLASS 1st OUNCE
0.24 ADDITIIONAL OUNCE

4 Ounces
=C2+(C5-1)*C3 TOTAL
USING ALL STAMPS
=C2 =INT(F8/C8) =C8*D8 =C6
=C3 =INT(F9/C9) =C9*D9 =ROUND(MOD(F8,C8),2)
0.01 =INT(F10/C10) =ROUND(MOD(F9,C9),2)
="USING "&$C$2*100& "c & 1c ONLY"
=C2 =INT(F12/C12) =C12*D12 =C6
0.01 =INT(F13/C13) =ROUND(MOD(F12,C12),2)
="USING "&$C$3*100& "c & 1c ONLY"
=C3 =INT(F15/C15) =C15*D15 =$C$6
0.01 =INT(F16/C16) =ROUND(MOD(F15,C15),2)
="USING "&C2*100&"c FIRST & "&C3*100&"c & 1c"
=C2 1 =C18*D18 =F8
=C3 =INT(F19/C19) =C19*D19 =$C$6-C2
0.01 =INT(F20/C20) =ROUND(MOD(F19,C19),2)


--
Don Guillett
SalesAid Software

"omutumo" wrote in
message ...

Hi,

I am trying to write a formula that automatically calculates postal
costs for items depending on their weight. However, there is one part
thats a bit more complicated.
If the item is over 1000grams the courier charges an additional £0.89
per 250g. The rest of the formula is easy (just using nested script and
<= functions) but I can't figure out how to do this in a similar manner
(ie. I don't want to have to write lots of ridiculously long nested
scripts!). grrr.

Thanks in advance for any help you can give me :)


--
omutumo
------------------------------------------------------------------------
omutumo's Profile:
http://www.excelforum.com/member.php...o&userid=34681
View this thread: http://www.excelforum.com/showthread...hreadid=544474

Postal formula
 

if you are charged this on the under 1000 grams as well as over
=current-formula + (A11000) * CEILING(A1/250,1) * .89

or if the charge only applies to the amount over 1000 grams
=current-formula + (A11000) * CEILING((A1-1000)/250,1) * .89

The condition A11000 is a test, it returns either 0 if false
or 1 if true.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"omutumo" wrote in message
...

Hi,

I am trying to write a formula that automatically calculates postal
costs for items depending on their weight. However, there is one part
thats a bit more complicated.
If the item is over 1000grams the courier charges an additional £0.89
per 250g. The rest of the formula is easy (just using nested script and
<= functions) but I can't figure out how to do this in a similar manner
(ie. I don't want to have to write lots of ridiculously long nested
scripts!). grrr.

Thanks in advance for any help you can give me :)


--
omutumo
------------------------------------------------------------------------
omutumo's Profile: http://www.excelforum.com/member.php...o&userid=34681
View this thread: http://www.excelforum.com/showthread...hreadid=544474