Results 1 to 13 of 13
Thread: Calculating Mileage (Excel 2003)

20060522, 14:34 #1
 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 coordinates 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 distancesquared 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 coordinates (in meters) from the above postcode file. I.e. you have Xa, Ya and Xb, Yb. Now what Pythagoras says is that:
distance = squareroot ( (XbXa)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:

20060522, 14:46 #2
 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, ...?

20060522, 14:50 #3
 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.

20060523, 09:15 #4
 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
StephenCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)

20060523, 12:17 #5
 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)

20060524, 09:43 #6
 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
StephenCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)

20060524, 09:45 #7
 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
StephenCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)

20060531, 10:03 #8
 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 enroute (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
SteveCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)

20060531, 10:25 #9
 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

20060531, 10:28 #10
 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]

20060531, 10:32 #11
 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.

20060531, 11:09 #12
 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]

20060531, 12:24 #13
 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
StephenCheers
Steve
Asking the questions everbody wants the answers too but feels too stupid to ask themselves :)