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

    Tariff Formula (2003 SP2)

    Good evening,

    I have attached a workbook example of a UK wide tariff that I am trying to implement. There are various mileage bands in B5:H9 and various destinations in A12:A14 and their corrosponding mileages in B12:B14. What I am trying to achieve is to select a destination in Combo 1 (linked to A2) a vehicle size in Combo 2 (Linked to C2) which will calculate a charge into F2 which would be based on the destination (A12:A14), vehicle size (A6:A9), mileage bands (B5:H9) and actual mileage (B12:B14)

    I hope that makes sense.

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

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

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

    Re: Tariff Formula (2003 SP2)

    I started by replacing the distance bands in C5:H5 with their lower bounds to have numbers to compare with.
    I then created several separate lookup formulas, and finally combined them in the following monster formula:

    =VLOOKUP(C2,A6:B9,2,FALSE)+VLOOKUP(A2,A12:B14,2,FA LSE)*INDEX(C6:H9,MATCH(C2,A6:A9,0),MATCH(VLOOKUP(A 2,A12:B14,2,FALSE),C5:H5))

    See the attached version.
    Attached Files Attached Files

  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: Tariff Formula (2003 SP2)

    Thank you Hans

    I have one small problem with this at the moment (apart from completely understanding it!!) and that is that it is calculating the mileage fine, but it is then adding the minimum what I would like, if possible is that if the minimum charge is greater than the calculated charge then the minimum applies. If the calculated charge is greater than the minimum then the calculated charge applies for example:-

    Combo 1 = No Town, Combo 2 = Small van charge should be 33.00 (no town mileage = 55 corresponding small van rate = 0.60 = 33.00, however the calculation at the moment is adding the minimum and making the total Mileage + Van Rate + Minimum = 48.00)

    I have attached another example with a town name that is less then the minimum charge mileage which hopefully will demonstrate it a bit better.

    Thanks

    Steve

    PS. Is there any good reading on compiling the intermediate formulas that you use so that I can try to do it myself?
    Cheers

    Steve

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

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

    Re: Tariff Formula (2003 SP2)

    I don't see an attachment, but I have attached a new version with (hopefully) the formula that you want, plus all the intermediate formulas.
    Attached Files Attached Files

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

    Re: Tariff Formula (2003 SP2)

    Wow, Thats fantastic Hans it hit the nail right on the head.

    Thanks for showing the intermediates it adds a logical dimension of how you constructed the final answer so that I can try to get to get grips with this kind of thing myself

    Thanks again

    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
  •