Results 1 to 13 of 13
  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

    Calculating Mileage (Excel 2003)

    Good afternoon

    I have an excel workbook which contains all of the first part of the UK's postcodes (eg. AB10, AB11 etc.) in column A I also have the X - Y co-ordinates in columns F and G. I would like to make a worksheet where I could type in one postcode into one cell, type in another postcode in another cell and have the 'Straight Line' mileage calculated. I found the following article regarding Pythagoris therom which leads me to beleive this could be done in an Excel workbook but I do not have the Excel or Maths skills, can any kind sole help me out?

    Pythagoras theorem
    This page is about trying to help you solve the particular mapping problem you have so I'm not going to go into the in's and out's of Pythagoras and how and why works.
    However, as a refresher for those dusty memory banks, Pythagoras allows you to calculate the straight line distance between two points, using a very simple formula. Basically the distance-squared is equal to the sum of the squares of the x and y distances between your points. It's hard to put in writing, so formulaically:
    Let's assume we have points A and B, and you have their X and Y co-ordinates (in meters) from the above postcode file. I.e. you have Xa, Ya and Xb, Yb. Now what Pythagoras says is that:
    distance = square-root ( (Xb-Xa)2 + (Yb - Ya)2 )
    This will give you the distance in meters. In actual fact it doesn't matter which way round you have the b's an a's because the square is always positive.
    Accuracy
    The observant among you will quickly note that the straight line distance calculated above is not exactly the straight line distance in the real world, because in the real world the surface of the Earth is curved. So, theoretically the distance you would travel is probably slightly longer.
    It's beyond the scope of this article to go into the detail, but suffice to say the above Pythagoras01 calculation is a good enough approximation for the following reasons:

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

    Re: Calculating Mileage (Excel 2003)

    What are the units for the X and Y coordinates in the table in columns F and G? Metres, Kilometres, Yards, ...?

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

    Re: Calculating Mileage (Excel 2003)

    IF X and Y are metres, see the attached workbook. As they say, your mileage may vary.

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

    Re: Calculating Mileage (Excel 2003)

    Hi Hans

    Thank you for the example, I have a problem however when I try to add extra postcodes (altogether there will be 2,800 when finished) it shows me that there is an error, I guess this is because I must somehow change the references in the formulas to reflect the fact that the list has become longer, when you have a moment could you look at the attached example and tell me where I have gone wrong or what I need to add to the formulas.

    So that I can try to get my (simple) mind around what is happening can you please explain to me the following

    1) column 'F' is never mentioned in the formula but its value appears in the 'X' axis value
    2) Where you have $G$5,6,False) I understand that G5 is the first value to lookup but what does the 6 refer to? I had assumed that this was how many items needed to be looked up in Column 'G' and changed it to 5,2800,False) but it errored.

    Any advice appreciated

    Stephen
    Cheers

    Steve

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

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

    Re: Calculating Mileage (Excel 2003)

    Please look up the help for the VLOOKUP function. The formula in cell K3 is

    =VLOOKUP(J3,$A$2:$G$5,6,FALSE)

    J3 is the cell containing the lookup value - the postal code.
    $A$2:$G$5 is the range to look in - it extends from column A to column G, and from row 2 to row 5. It is clearly the 5 that has to be changed now that there are more rows. In the example you attached, that data extend to row 33, so you'd have to use $A$2:$G$33, and if the real data extend to row 2800, change it to $A$2:$G$2800.
    6 is the column index in the range that will provide the return value. Since the range begins in colum A, the 6th column is column F, containing the X values.
    FALSE tells Excel to look for an exact match of the lookup value; if you specify TRUE or omit this argument, Excel will look at the highest value that is less than or equal to the lookup value.

    The formulas in K4, L3 and L4 are similar (with 7 as column index for the formulas in column L)

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

    Re: Calculating Mileage (Excel 2003)

    Thanks Hans

    Excellent explaination, thanks a lot, I am now experimenting with adding via's so if you don't mind I may need some more advice later

    TFN

    Stephen
    Cheers

    Steve

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

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

    Re: Calculating Mileage (Excel 2003)

    Thanks for the terrific explaination Hans

    Stephen
    Cheers

    Steve

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

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

    Re: Calculating Mileage (Excel 2003)

    Hi Hans

    If you have a moment could you please take a look at the attachment for my mileage calculator, I have tried to adapt your example to include stops en-route (via's) which does work if all stops are completed, Is there anything I can do to arrive at the total mileage if only some or none of the via's are completed.

    Thank you

    Steve
    Cheers

    Steve

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

  9. #9
    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: Calculating Mileage (Excel 2003)

    I am not sure I understand what you need exactly.

    You can calculate the distance directly fom the first to the last, but this is the direct line answer and will be the minimum (straight line) distance. Without the intermediate routes, how are we to know what route was taken? This intermediate routes are what adds to the total.

    Given a start and end code, what is the logic that the formula/routine will use to arrive at the total distance?

    Steve

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Calculating Mileage (Excel 2003)

    Hi steve,

    Try the attached modifications to your worksheet.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Calculating Mileage (Excel 2003)

    That will work if the first n post codes are filled in, and the rest (including the End Post Code) is left blank. But if the End Post Code is filled in, but not all intermediary ("via") post codes, the result will be incorrect.

  12. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Calculating Mileage (Excel 2003)

    Hi Steve,

    Update attached to take account of Hans' observation that the previous version didn't handle the last row being filled in but not some of the rows above it.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Calculating Mileage (Excel 2003)

    Thank you Macropod, thats perfect

    Hans, thanks as usual for your input, Steve, I will already know the order that deliveries will be made in

    Cheers


    Stephen
    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
  •