Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula assistance please (Excel 2000)

    How about this one:

    =CHOOSE(MIN(INT((B12-1)/50)+1,7),1.2,1.1,1,0.95,0.9,0.85,0.8)*B12
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #6
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #8
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •