# Thread: Calculating Mileage (Excel 2003)

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

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

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

Stephen

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

7. ## Re: Calculating Mileage (Excel 2003)

Thanks for the terrific explaination Hans

Stephen

8. ## 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

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

Hi steve,

Try the attached modifications to your worksheet.

11. ## 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. ## 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.

13. ## 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

#### Posting Permissions

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