Maximum Distance Between Lat/Long Points

Hi there,

I was wondering if anyone could assist with the following. I have a list of buildings that are divided into regions and sub-regions. There are 34 sub-regions (26 active ones). What I am trying to figure out is how to calculate the maximum distance from one building to another within each sub-region. This will help to find out the maximum distance the sub-region supervisor will have to travel.

Any help on this is highly appreciated

I have attached the list of the buildings with the accompanying sub-region as well as the GPS coordinates.

Are you extracting this from a database or do you just have it in Excel, this is easier to do in sql rather than with formulas, but it is still doable. Is this a one off Job, or will you need to keep adding sites/areas?

Also, are you using 2007 or 2003? Your profile says 2007, but that's an '03 book

The excel was extracted from a intranet database, but I don't have super user access, I can only view and extract data onto Excel sheets, hence the only tool I have to work with is Excel.

I believe this will be a one off exercise. I'm using excel 2010 (need to update my profile) but it seems the intranet database exports to 2003.

Ok if it's a one off job then you can do it the brute force way There are more elegant ways, but are more complicated so will take longer to set up.

The formula for getting the as the crow flies distance (in KMs) is:

PHP Code: 

=ACOS(SIN(LAT1)*SIN(LAT2)+COS(LAT1)*COS(LAT2)*COS(LON2-LON1))*6371 


In a new workbook, create a matrix using the above formula to get all the distances from all the sites to all the other sites. (all the sites down the left, then transpose them across the top, you can then use VLookups to fill in the values for the above formula).

Copy the formulas and paste special with values to keep your workbook snippy - there'll be about 70k cells.

You can then get the max value from each block of sites - that will be the greatest distance between any two points in that block/zone

The formula was giving me off numbers, I used this one instead that I found

PHP Code: 

=6371.1*((2*ASIN(SQRT((SIN((RADIANS(lat1)-RADIANS(lat2))/2)^2)+COS(RADIANS(lat1))*COS(RADIANS(lat2))*(SIN((RADIANS(lon1)-RADIANS(lon2))/2)^2))))) 


However still not sure how to use the matrix method you mentioned above.

Put the site Ids down the left of a sheet, put the same Ids along the top (to form 2 sides of a square), That formula goes in the contents of the square, so if your square starts in column A, A2 would be 1075, B1 would be 1075 etc and B2 would be your formula

Use Vlookup to fill in the long and the lats. So for Lat1/Lng1 you'd use the sites down the side as the lookup value and for Lat2/Lng2 you'd use the sites along the top. Then drag your formula to fill in every cell in the square

Would I use hlookup for the values at the top?

I think it worked! Thanks alot, it just took me a while to figure out how to embed the vlookup and was too embarrassed to ask. Thanks again!

Glad you got it working if have replied, but it's a bit early, 0700 here! Let us know if you need help with the formulas to find the maximum distance in the zone

As you put it, I used "Brute Force" too, with some lazy concatenate formula to figure it out. It's really ugly but it works. Thanks!

no problem

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save