Distance between Coordinates

[dustinh48625]

I have created a sheet that shows distance between coordinates. Also working on something to put them in order of the closest point. I used a fixed point to find the nearest coordinate, but since i'm using a fixed point it doesnt go the order I want it to. I have attached the excel file I am working on. Please add any input Maybe a formula to find the nearest point from a list of coordinates to order each??Excel Help Distance.xlsx

[xladept]

Hi Dustin,

Could you put in some headers so that we can make out what it is that you want?

[dustinh48625]

I added headers to each.

Column M I use to search the distance each coordinate is from Q1 & R1, which I am able to sort column M after that with the shortest being on top. Column N shows the distance from the row below coordinates and column O adds the distances together.Excel Help Distance.xlsx

[xladept]

Deleted as impertinent and Ill advised - Sorry!

[dustinh48625]

I'm not really sure whats the best way to complete this. From all the research I did this is the only thing I can find and I'm sure there is something better that could be used. Let me know your thoughts to make it more correct.

[xladept]

Hi Dustin,

Ignore my last post, your distance is copacetic with the change in latitude as a degree of latitude is about 69 miles - but, the truth is: I really don't understand what you're trying to do - it seems like your formulas are correct. I'm deleting my last post

[dustinh48625]

I'm trying to complete 2 things.

1. First I need to sort the coordinates so they go in order from a starting point moving to the next closest point until it reaches the end.

2. Once they are in order I will calculate the distance between the coordinates so I can show the full distance in feet.

The coordinates aren't always the one provided in the sheet so I deal with many different coordinates. If possibly I wouldn't mind calculating distance in 3D rather then 2D which is what I have now.

[dustinh48625]

Also sometimes the coordinates aren't in a straight line so the method I use to figure out the order of coordinates might not always work. So if there might be a formula that would go in order and finding the next coordinate nearest to it would be helpful.

[xladept]

Here's a vector lesson I presented several years ago - I expect that you already know this but for x coming out of the diagram, z going up on the diagram and y traversing left to right on the diagram

I'm not sure if this will help - but you're already in 3D - anyway:

z=r sin lat; x=r cos lat cos long and y= r cos lat sin long

DustinVex.jpg And here's your file:DustinsDistances.xlsx

[xladept]

Hi Dustin,

I just got a straight line distance greater than the GC Distance - have you considered putting elevation into your data set??

[dustinh48625]

I have considered it...could you guide me on this?

[xladept]

Well you'd need latitude, longitude and elevation for each site then just add the elevation to the earth's radius (6371 km), commensurably of course - but, I'm still not getting what you're after?

BTW - I suspect that the .31 feet is just rounding error??