+ Reply to Thread
Results 1 to 3 of 3

Postal formula

  1. #1
    Registered User
    Join Date
    05-22-2006
    Posts
    1

    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

  2. #2
    Don Guillett
    Guest

    Re: 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
    [email protected]
    "omutumo" <[email protected]> wrote in
    message news:[email protected]...
    >
    > 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
    >




  3. #3
    David McRitchie
    Guest

    Re: Postal formula

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

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

    The condition A1>1000 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" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1