Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    half kilo calculations (E2000)

    I am working on a courier tariff that has a charge for the first full kilo, a half kilo rate for weights under 15, a half kilo rate for weights between 15 and 30 and then a half kilo rate for weights above 30 kilos, I have the following columns

    (a) destination ([img]/forums/images/smilies/cool.gif[/img] first kilo <15 per half (d) >15<30 per half (e) >30 per half (f) Enter Weight (g) Total Charge

    I have tried the following

    =IF(F3<15,F3-1*C3*2),IF(F3>15<30,F3-1*D3*2), IF(F3>30,F3-1*E3*2) the -1 is to compensate for the first full kilo charge in Column B

    Which returns a #VALUE error and I cannot see why, also typing this I realise that I will have a problem because my formula will always charge for a full kilo because of the *2 instruction.

    Any help (as usual) greatfully received


    Stephen

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: half kilo calculations (E2000)

    How about this:
    =IF(F3<15,F3-1*C3*2,IF(F3<30,F3-1*D3*2,F3-1*E3*2))

    IF F3<15 then you get:
    F3-1*C3*2
    If F3>=15 then you go to the 2nd IF:
    So if F3>=15 (implicit from first IF) and F3<30 (explicit from 2nd IF)
    Then F3-1*D3*2
    Lastly if if F3>=15 (implicit from first IF) and F3>=30 (explicit from 2nd IF)
    You will get: F3-1*E3*2

    You could also use:
    =F3 - 2* IF(F3<15,C3,IF(F3<30,D3,E3))

    Which should give you the same result. <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> the "1" does nothing in the calcs.

    If this is not correct logic, could you give some example calcs and how you would do them manually so we could figure the formula from examples?
    Steve

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: half kilo calculations (E2000)

    Hi Steve

    Perhaps the attachment shows a clearer example of how I would like this to work, basically I have a first kilo rate of say

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: half kilo calculations (E2000)

    This one, then?

    =B3+IF(F3<15,2*(F3-1)*C3,IF(F3<30,2*(F3-1)*D3,2*(F3-1)*E3))

    You may want to use <= instead of <, it is not clear from your description what happens if the weight is exactly 15 or 30 kg.

    The formula assumes that if the weight is over 15 kg, everything except the first kg is charged at the "Over 15 (per half)" rate, and similarly if the weight is over 30 kg, as in your example.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: half kilo calculations (E2000)

    Thank you Hans but in a way that works to perfectly in so far as if a user was to say input a weight of 14.5 kilos to say Belguim this formula correctly returns the result of

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: half kilo calculations (E2000)

    Put the following formula in H3 and fill down:

    =CEILING(F3,0.5)

    This rounds up the weight to the nearest half kg. Then change the formula for the tariff to

    =B3+IF(H3<15,2*(H3-1)*C3,IF(H3<30,2*(H3-1)*D3,2*(H3-1)*E3))

  7. #7
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: half kilo calculations (E2000)

    Thanks Hans spot on as usual


    Stephen

Posting Permissions

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