Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculation Problem (XP2003 SP2)

    Good afternoon

    I have several problems which I have been trying ro address for about 2 weeks but my amateurish attempts are proving no good

    I have a worksheet for a courier tariff, the following is a brief description

    1) Shipments are charged on weight or volume whichever is the greater to the nearest rounded up 1/2 kilo with a minimum of 1/2 kilo
    2) You will notice in the example that for some destinations there is a cheaper rate for 1 kilo (rather than applying the 1/2 kilo rate)
    3) There is no extra charge for a standard delivery but there is a surcharge if a timed delivery is required

    In my feeble attempt at a formula I have tried to look up the country in conjuction with the 1/2 kilo rates (I had to ignore the issue mentioned in (2) above because I could not even begin to think how to address it) multiply it by the chargeable weight (which in my example would have only been for whole kilos) and then add any surcharge for a timed delivery ((3) above))

    If somebody could lend a hand it would be most appreciated

    Thanks

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calculation Problem (XP2003 SP2)

    Steve,
    A couple of things:
    1. What are the -15, +15 and +30 columns? (are they for the first 15 extra half kilos etc.)
    2. None of the examples have a kilo rate lower than the half kilo rate?
    3. Someone can't spell Belgium! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Problem (XP2003 SP2)

    Thanks Rory, that seems to be almost perfect

    Steve,
    A couple of things:
    1. What are the -15, +15 and +30 columns? (are they for the first 15 extra half kilos etc.)
    If a client has for example 10 kilos he would be charged the -15 rate for each half kilos, if he has 16 kilos the +15 kilo rate would apply
    2. None of the examples have a kilo rate lower than the half kilo rate?
    Badly described by me: In for example B20 if a Customer only has 1/2 a kilo he will pay
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calculation Problem (XP2003 SP2)

    Rough guess is you want something like the attached - note that it will not cope with the delivery options where you have text entries rather than numbers.
    Hope it gets you started.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Calculation Problem (XP2003 SP2)

    Is the attached closer?
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculation Problem (XP2003 SP2)

    Fantastic, thanks so much Rory, I am still trying to understand how most of it works!!

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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