1. 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

2. 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.

3. 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?

4. 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.

5. 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

Posting Permissions

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