# Posts by zzgorme

• ## Distance between coordinates, correct formula

Re: Distance between coordinates, correct formula

Yes I found the error, but it there is still a divergence from Google Earth. Maybe they are calculating with the Earth not being a perfect sphere.

• ## Distance between coordinates, correct formula

Re: Distance between coordinates, correct formula

Thanks I worked out how to autofill the rows but it is giving some wrong answers as well. It may be when the second longitude is larger than the first longitude. For example on Google Earth I plotted

2 -13.9865 107.831 3 -48.0027 55.505

That's the 2nd and 3rd set of coordinates in the spreadsheet. Both in the Southern hemisphere hence the minus sign and both West longitude. In the spreadsheet I get a distance of 5304 miles which is 8535 kilometers. On Google Earth I use GEPath to calculate the distance between these as 6172 kilometers. However the first one was right.

• ## Distance between coordinates, correct formula

Re: Distance between coordinates, correct formula

Rob, it was a bit easier because I was worried about converting to radians with north and south latitudes not getting confused. This seems to work properly as well, I tried it with one set with a south latitude and the second set of coordinates with a north latitude. How can I generalize this for all 455 combinations, this seems to work for the 21st row only.

• ## Distance between coordinates, correct formula

Re: Distance between coordinates, correct formula

I think it's right with the second formula you give. I plugged in some coordinates that I have on Google Earth and it was within about 60 miles of the right answer. They probably have a different number for the radius of the Earth. I was wondering how I can use this for each set of coordinates, there are 455 in all. This works for the 21ist row, is there a way to generalize it for each row without having to adjust the row numbers manually? Thanks.

• ## Distance between coordinates, correct formula

Re: Distance between coordinates, correct formula

Junho, I tried it but it didn't work. Maybe you can add it into the spreadsheet:

http://www.gregorme.com/scratch.xls

Rob, it looks like I may have to convert to radians as you suggest.

• ## Distance between coordinates, correct formula

Hello,

I needed to work out the combinations of 15 locations in groups of 3 as explained here:

http://www.ozgrid.com/forum/sh…46301&p=522971#post522971

This gives a large number of spherical triangles. Next I need to use the Haversine formula to work out the distances between these coordinates, i.e. the lengths of the sides of each spherical triangle. I think this formula will work:

3963.0 * arccos[sin(lat1/57.2958) * sin(lat2/57.2958) + cos(lat1/57.2958) * cos(lat2/57.2958) * cos(lon2/57.2958 -lon1/57.2958)]

This works from the coordinates in decimal degrees directly rather than converting them to radians. Is it possible to use this formula in Excel? Lat1 is E, Long1 is F, Lat2 is G and Long2 is H. I may have to convert the coordinates into radians first and use this formula:

3963.0 * arccos[sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(lon2 - lon1)]

I'm not sure how these formulas work with north and south latitudes, north is usually written as positive and south as negative but perhaps they need to be written as 180 degrees. For example the south pole would be 0 degrees and the north pole 180 degrees.

• ## Permutation of two sets of 15 numbers combined groups of 3

Re: Permutation of two sets of 15 numbers combined groups of 3

I think I worked it out. I copied the last row that had numbers in it, row 29, and special pasted the formulas down to row 455. That looks correct, thanks a lot. One last question, if I wanted to adjust this so there were more or less than 15 coordinates, say for example 12 or 20 how can I adjust this?

• ## Permutation of two sets of 15 numbers combined groups of 3

Re: Permutation of two sets of 15 numbers combined groups of 3

How do I drag them down?

• ## Permutation of two sets of 15 numbers combined groups of 3

Re: Permutation of two sets of 15 numbers combined groups of 3