Posts by zzgorme

  • 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

    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

    Hello,
    I realize other people have asked about this, but I can't see how to adjust the answers given for this case. I have a list of 15 coordinates, i.e. 15 latitudes and 15 longitudes. I can place them in the first 2 columns A1 and B1. I need to have all the combinations listed in columns C1, D, and E1 for latitudes and F1, G, and H1 for longitudes. That's the major problem for me, that gives me groups of 3 coordinates which would represent spherical triangles on a globe. Then I want to use the Haversine formula to calculate the lengths of each of the three sides, these might end up in column I1, J1, and K1. Then I think I need to resort the lengths from the largest to the smallest, these might be in L1, M1, and N1. Then I need to work out the angles in each triangle, I have the formulas for that. The main sticking point is the combinations.