1. ## Formula assistance please (Excel 2000)

Good morning

I am new to the group and relatively new to Excel. I am trying to make a transport tariff for domestic carriage throughout the UK, I have 6 columns which are

A = Postcode into which I have typed all of the UK post code areas
B= The Mileage from Heathrow to each of these postal areas
The remaining 4 are for different vehicle sizes.

The transport charges are based on distance bands, i.e. 25-50, 51-100, 101-151 miles etc.

I have tried the following but cannot get it to return anything but a 0

=IF(B12>25<50,B12*1,20,IF(B12>51-100,B12*1.10,IF(B12>101<150,B12*1.00,IF(B12>151<20 0,B12*0.95,IF(B12>201<251,B12*0.90,IF(B12>251<300, B12*0.85,IF(B12>300,B12*0.80,"")

I would also like the formula to recognise that if a mileage is less than 25 miles it has to be charged as per the first band rate.

Any ideas greatly appreciated.

Danny

2. ## Re: Formula assistance please (Excel 2000)

Welcome to Woody's Lounge!

I'm not sure I understand what you want to happen if the distance is 25 miles or less, but see if the attached workbook does what you want. The basic idea is to use a lookup table for the tarriff bands, and a VLOOKUP formula.

3. ## Re: Formula assistance please (Excel 2000)

=CHOOSE(MIN(INT((B12-1)/50)+1,7),1.2,1.1,1,0.95,0.9,0.85,0.8)*B12

4. ## Re: Formula assistance please (Excel 2000)

Hi Thank you for your assistance

What you have done with the less than 25 kilos is exactly what I wanted, I am having problems though in getting it to work correclty in my workbook. I beleive that I have shown the range in the correct manner but whatever mileage is entered in column B the tariff rate still shows as

5. ## Re: Formula assistance please (Excel 2000)

Make sure that your workbook is below 100 KB in size. If it's too large, zip it and attach the zip file.
Don't preview your post after filling in the 'Attach a file' box.

6. ## Re: Formula assistance please (Excel 2000)

Thank you,

I have now managed to reduce it to a manageable zip size which hopefully still shows what I am trying to do.

Thanks

Danny

7. ## Re: Formula assistance please (Excel 2000)

In the first place, you changed the orientation of the lookup table from vertical to horizontal, so you should use HLOOKUP instead of VLOOKUP.
In the second place, you must use numeric values to look up. A value such as 101-150 is text, not a number. So I added the numeric values in row 1, and adjusted the formula accordingly. See attached workbook. (I deleted the unused rows and columns, resulting in a workbook of only 16KB)

8. ## Re: Formula assistance please (Excel 2000)

Thank you, Thats great

I did not realise the significance of H and V in the lookups but that makes sense now, using your example I managed to add in each of my other vehicle sizes so that the rates are now calculated by mileage and van size.

Thanks again

Danny

#### Posting Permissions

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