Saturday, January 06, 2007

Excel formula to calculate distance between 2 latitude, longitude (lat/lon) points (GPS positions)

I've been looking for this one for ages, and the one I created based on an equation I found a little while ago didn't work too well :(

Investigation

Calculate distance, bearing and more between two Latitude/Longitude points shows lots of equations in math, and implemented in JavaScript. Chris Veness's page also contains an Excel formula of the ‘Haversine’ equation (actually, using the "spherical law of cosines") for distances between points in kilometres:

=ACOS(SIN(Lat1)*SIN(Lat2) +COS(Lat1)*COS(Lat2)*COS(Lon2-Lon1)) *6371

But it wasn't working, because my GPS points (Lat/Lon) were in decimal degrees, not radians. So I used the formula I found at Latitude And Longitude In Excel (under the "Great Circle Distances" section) by Chip Pearson:

=RadiusEarth*ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2)))

where Chip gives RadiusEarth as 6378.135 kilometres. This didn't match up with distances I had when calculated from other systems. But Chris's page has:

R = earth’s radius (mean radius = 6,371km)

Digging deeper, I found Chris's Vincenty formula for distance between two Latitude/Longitude points page which includes a table on different datum models (treating Earth as an ellipsoid), it shows WGS-84 & GRS-80 having the greatest radius on an ellipsoid as 6378.135km & the smallest as 6356.752km.

So Chip was using the maximum radius of the Earth, not the mean radius like Chris (I'm not sure where Chris gets the mean radius from). Substituting Chris's R for RadiusEarth in Chip's formula gives:

Solution

=ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) *6371

and my final version that uses Excel cell references is:

=ACOS(COS(RADIANS(90-A2)) *COS(RADIANS(90-A3)) +SIN(RADIANS(90-A2)) *SIN(RADIANS(90-A3)) *COS(RADIANS(B2-B3))) *6371

PS. To calculate distances in miles, substitute R (6371) with 3958.756 (and for nautical miles, use 3440.065).

339 comments:

  1. You are the man. After much searching, your post gave me just the Excel formula I needed, with the correct distances (as measured at that moveable-type site)

    I'm trying to visit every Larosas, Busken, Graeters and Skyline in Cincinnati in one day, and was trying to figure out distances

    dan.
    www.everywhatever.com

    ReplyDelete
  2. Thanks so much, this is exactly what I was looking for.

    ReplyDelete
  3. Thank you- this, plus the batch calculator at www.batchgeocoder.com make for a powerful combination... Saved me loads of research!!!

    ReplyDelete
  4. You made my day!
    Thanks a lot!

    From a happy Norwegian

    ReplyDelete
  5. Thanks so much, it saved me hours.

    ReplyDelete
  6. I'm setting up mini-conferences around the country, and your formula made it easy to convert a database of zip code lat/lon figures into distances. Now I can sort cities instantly by distance from a point. Thanks! Mark Lee

    ReplyDelete
  7. Impressive ! Thanks a lot, I needed 15 minutes to tweak it the way I need instead of hours of copy/paste ! Thanks :))

    ReplyDelete
  8. Thanks for the information.
    I am new to excel, could you give a sample file ?
    how do i define the columns ?
    thanks a million in advance
    gabriel

    ReplyDelete
  9. I think the formula needs one more set of brackets around the latitude part of formula.

    ReplyDelete
  10. could you give an example
    excel layout with the data typed?
    input as google earth ?
    gabriel

    ReplyDelete
  11. This is what I'm looking for, thanks.
    Distance between two points with curvature of earth accounted for.

    Thanks very much.

    ReplyDelete
  12. BluMM,
    I need a variation on this. Given a set of coordinates, how do I calculate the coordinates one mile away at a given bearing? Any ideas?

    ReplyDelete
  13. Thanks a lot! You really really made my day. I was constructing a distance variable between households and health facility to use it in a service use prediction in stata. I spent time looking for a command i stata (i don't have a GIS soft)...the I found your formula). Thanks a million!

    ReplyDelete
  14. Excellent, just excellent. Thanks very, very much.

    ReplyDelete
  15. Perfectamundo. I downloaded some Lat/Long data by zip code and imported it into my SQL database. I then tweaked your formula to be able to use it in Crystal Reports.

    ReplyDelete
  16. Wonderful. Thanks a lot. Could you please gave the other formula for calculating the angle between two coordinates.

    ReplyDelete
  17. Hello readers

    This is very close to what I am looking for. However, I need a formula that does the job in another way, so to say :-)

    Given:
    Lon1, Lat1, radius, angle

    looking for:
    lon2, lat2

    where do I land (lon2, lat2) with the above mentioned figures (under "given")?

    Can anyone help me with this?

    Best regards,
    Ralph

    ReplyDelete
  18. that is so so frickin cool. thank you

    ReplyDelete
  19. You've saved me so much effort - thanks!

    ReplyDelete
  20. Thank you..Very Simply

    Alvaro...From Brasil/ São Paulo.

    ReplyDelete
  21. Thank you very much. This is excellent!! Saved so much time. God bless you

    ReplyDelete
  22. Can anyone send me a working copy of the Vincenty formual in excel?

    I've already got a basic formula / excel work book for calculating the distance between two airports (using their respective lat/longs read from separate table) but I'm no genius when it comes to tweeking the formula to accomodate the changes by Vincenty.


    Any help greatly appreciated.
    Regards,

    Fergusflan@gmail.com

    ReplyDelete
  23. Finally found what I need.. thank you so much!!!

    ReplyDelete
  24. OMG, you are the master of the masterss.. it save me a lot of time and extra points with my boss.

    ReplyDelete
  25. This is AWESOME... though i'd love to see how to calculate travel-distance between 2 zip codes/2 lat/longitudes

    ReplyDelete
  26. Thanks a lot. Saved a lot of time.

    ReplyDelete
  27. Awesome ! Great formula and even better description.

    ReplyDelete
  28. Thanks for creating a very useful tool! You made my day!

    ReplyDelete
  29. I cannot believe how simple you made this look! i sent hours looking at other formulas and when I tried to convert it it was always the wrong answer

    It nice that the internet is used for such great resources and that people like are willing to share their wonderful skills

    ReplyDelete
  30. I cannot believe how simple you made this look! i sent hours looking at other formulas and when I tried to convert it it was always the wrong answer

    It nice that the internet is used for such great resources and that people like are willing to share their wonderful skills

    ReplyDelete
  31. You just saved me doing this manually 260 times. I owe you a MASSIVE beer.

    ReplyDelete
  32. this is great-can it be tweaked into Crystal?

    ReplyDelete
  33. Thanks for sharing this formula! There is just no way I could have figured this out on my own.

    ReplyDelete
  34. This is great. But how do I use it in Crystal?

    ReplyDelete
  35. Very helpful in the current project at work.

    I did notice that the radius of earth noted does not match up with Google's. They appear to be using 3963.1676 mi as the earth's radius to calculate distance between locations in miles. I am using this radius in calculating my distances to coincide with Google's.

    ReplyDelete
  36. First Thanks in 2010. Thanks!

    ReplyDelete
  37. Hi,
    Thank you for making this easy in excel,but how do you do it in crystal report? Please help.
    Thanks.

    ReplyDelete
  38. I don't think this works between places that are separated by the prime meridian or the equator...or did I miss something?

    ReplyDelete
  39. Thanks a lot. Saved loads of effort

    ReplyDelete
  40. Fantastic! I am involved in tagging butterflies and was just settling down for a few hours reading to figure out how to do this when I found your answer presented on a silver platter!

    ReplyDelete
  41. Awesom!!!

    ... and that's why we love the web. Sharing knowledge.

    ReplyDelete
  42. Thank you. You saved me so SO much work.

    ReplyDelete
  43. Brilliant...simply brilliant...

    ReplyDelete
  44. I spent 2hrs on this yesterday and failed. With your help I spent 5 minutes today and it worked a treat. Many thanks!

    ReplyDelete
  45. Just wonderful! Thanks so much.
    This helped me set up a database which calculated the closest branch to every telephone exchange in the country.

    ReplyDelete
  46. I was getting no where with co-ordinates in degrees, excel and the haversine formula until i found your page, after which my quest was complete. Many thanks.

    ReplyDelete
  47. Three years later and you're still being thanked! I have to thank you, too! This is incredible!!

    ReplyDelete
  48. Wow, I am very much in your debt. I tag rockfish and this task of calculing their movement was a mountain of a task. You have made it easy. Thank you very much.

    ReplyDelete
  49. Ditto what everyone else has ever said about your good character, name, intellect, the cut of your jib, etc, etc. THANK YOU!!!

    ReplyDelete
  50. Amazaing work.... Hats off mate

    ReplyDelete
  51. Amazing work.... Hats off mate

    ReplyDelete
  52. Finally. Been combing the internet for ages looking for something like this. I couldn't get any of the other formulas I tried to work

    Thank you so much

    ReplyDelete
  53. Really great stuff! I did a lot of investigation with poor results before finding this. A big thanks for the knowledge and sharing it!!!

    ReplyDelete
  54. Thank you for posting this little piece of genius...

    ReplyDelete
  55. Marvellous....

    Of course.. I now want to move to the next level... :)

    My next search, and indeed work myself, is to try to get distances to travel by road, and not just as the crow flies.

    ReplyDelete
  56. I spent HOURS (2 working days) on searching for other formulas that wouldn't work. Yours worked in 2 minutes and saved the day.

    This IS the best. Thanks a TON!!!

    ReplyDelete
  57. Perfect mate, cheers. Will vote up this result in the googles.

    ReplyDelete
  58. AWERSOME. Saved me a bunch of time to have this already done for me. Thank you!

    ReplyDelete
  59. Thanks for your post. It sounds like you're interested in making something like the highway mileage charts sometimes shown on paper maps, where you can find the distance between any two points that are included in the chart. Is that right? I don't believe that we have any such functionality in Google Maps right now, though that's an interesting feature suggestion, and I can pass it on to the relevant team.

    ReplyDelete
  60. As with so many others, this EXACTLY solved my problem, saving me hours and hours of time.

    In the spirit of giving back, here are some sample row equations if you're trying to build a "Triple-A Road Atlas" of city-to-city distances.

    This assumes a grid with city names across the top and left columns, Decimal Latitude in column B, and Decimal Longitude in column C.

    Cell $B$28 is the arbitrary cell storing the planet's diameter (since that seems subject to debate).

    Column D =ACOS(COS(RADIANS(90-$B$2)) *COS(RADIANS(90-B3)) +SIN(RADIANS(90-$B$2)) *SIN(RADIANS(90-B3)) *COS(RADIANS($C$2-C3))) *$B$28
    Column E =ACOS(COS(RADIANS(90-$B$3)) *COS(RADIANS(90-B2)) +SIN(RADIANS(90-$B$3)) *SIN(RADIANS(90-B2)) *COS(RADIANS($C$3-C2))) *$B$28
    Column F =ACOS(COS(RADIANS(90-$B$4)) *COS(RADIANS(90-B2)) +SIN(RADIANS(90-$B$4)) *SIN(RADIANS(90-B2)) *COS(RADIANS($C$4-C2))) *$B$28
    Column G =ACOS(COS(RADIANS(90-$B$5)) *COS(RADIANS(90-B2)) +SIN(RADIANS(90-$B$5)) *SIN(RADIANS(90-B2)) *COS(RADIANS($C$5-C2))) *$B$28
    Column H =ACOS(COS(RADIANS(90-$B$6)) *COS(RADIANS(90-B2)) +SIN(RADIANS(90-$B$6)) *SIN(RADIANS(90-B2)) *COS(RADIANS($C$6-C2))) *$B$28
    Column I =ACOS(COS(RADIANS(90-$B$7)) *COS(RADIANS(90-B2)) +SIN(RADIANS(90-$B$7)) *SIN(RADIANS(90-B2)) *COS(RADIANS($C$7-C2))) *$B$28
    Etcetera...

    First paste the column formula into the topmost cell of the column, then copy that cell and paste it down the length of the column. I'm not a good enough Excel Maven to automate this into a single equation that can be pasted throughout the NxN city matrix

    Thanks!

    ReplyDelete
  61. By the way, since the equations are unit-neutral, you can easily come up with separate distance tables in Miles and Kilometers by changing the value of the Radius from one to the other.

    ReplyDelete
  62. pleas expalin what is in the cells A2, A3, B2 and B3.

    Thanks Anthony fba@fbatm.biz

    ReplyDelete
  63. I should have read more. I now understand what A2 A3 B2 and B3 are and it works a treat. Please ignore mt query

    ReplyDelete
  64. I love you, Im totally i love with you ;-) Thanks

    ReplyDelete
  65. What format do you use to enter the lat/lon into Excel? Foe instance, what would 56°57′09.58″
    look like in Excel?

    ReplyDelete
  66. Spent hours trying to make sense of other sites. Found yours, tweaked and had it doing exactly what I needed in minutes!

    Thank you!

    ReplyDelete
  67. Is anyone else having troubles for areas in the south? I have verified that all of my coordinates are correct, but when I try to calculate a distance between 49512 and 33024 I only get 390 miles... It should be quite a bit more from Michigan to Florida!

    ReplyDelete
  68. This is great. Is there a version that calculates bearing?

    ReplyDelete
  69. Thanks a lot! Any chance also for the formula to determine the direction/bearing/azimuth from Pt A to Pt B ?

    ReplyDelete
  70. This worked great! Thanks from Ontario!

    ReplyDelete
  71. thankyou thankyou....!

    (to add to the million other thankyous on this comment page)

    ha ha and I'm also from Wales like the guy above...

    these Welsh fellas stick together! What are the chances of finding them like this???

    ReplyDelete
  72. Thanks - you've made a long and complicated job simple. Brilliant.

    ReplyDelete
  73. Thanks from sunny Milton Keynes. Saved me a load of time

    ReplyDelete
  74. Excellent!
    Thank you very much indeed for making it available to the rest of us.

    ReplyDelete
  75. AWESOME!!! I have spent hours trying to tweak the formula to work in Excel. Your post made it "copy/paste"! Thanks!

    ReplyDelete
  76. I'm putting the following coordinates, but it's not working:
    A2: -23502253 B2: -47488436
    A3: -23526376 B3: -47464113

    Can someone please help me?

    ReplyDelete
  77. Thank you so much, this is exactly what i needed

    ReplyDelete
  78. Consider me the second happy Norwegian (at least) you've helped out!

    ReplyDelete
  79. Thank you. I needede to calculate about 1000 flights for my company's carbon footprint. Used a simpler formula that was quite good but slightly inaccurate, did for example return different results when switching the destinations around. Then I found your solution that does the job presisely and was also easy to put into VBA code. After recalculating the 1000 flights the difference was only 0,001% or 90 kg CO2 !! Whatever, always good to have the best methodology in use ! Will certainly look for more tips & tricks on your blog !

    ReplyDelete
  80. Thank you, wish I had found your site before all the other I looked up first !
    Another happy Norwegian..

    ReplyDelete
  81. you are heaven-sent! :) thanks man.

    ReplyDelete
  82. does this formula work for 2 points on a bell curve ...i need to calculate the distance between to points on a bll curve?
    thx

    ReplyDelete
  83. Thank you very much. By combining your formula and http://www.excelforum.com/excel-programming/537429-closest-neighbour-coordinates.html for coordinate lookup, I was able to do reverse geo coding withing excel. Great job.

    ReplyDelete
  84. Brilliant ... now I can easily compute the distance between the Volvo yachts racing around the world!

    ReplyDelete
  85. its coolest investigation!, but i just want know how about calculate bearing or azimuth?

    great regard :)
    engkin
    eloknegerikuindonesia

    ReplyDelete
  86. Amazing. I applaud your hard-work and know how as im sure you dedicated a lot of time and energy to perfecting the fomrula. Comparing the distance from Toronto-Denver against geobytes, the formula was accurate within 3.75 miles; 99.8% accurate! Again, amazing. Endless thanks.

    ReplyDelete
  87. I looked at over 50 websites on this topic today - and this is the only one that was clear and made sense, thanks!

    ReplyDelete
  88. thank you very much. I've been racking my brain on this all day.

    you are a genius among men.

    ReplyDelete
  89. From a smallish charity in Australia: CHEER'S MATE!!

    ReplyDelete
  90. Thanks for posting this blog .This was so much helpful and was time consuming for mines to calculate the question using your tips .

    ReplyDelete
  91. Thanks!... This formula gives distances in km?..

    ReplyDelete
  92. Thanks brother! This is exactly what I was looking for!

    ReplyDelete
  93. Thanks so so so so much buddy!! You are the man!!! You saved like a million person hours ;)

    God bless you

    ReplyDelete
  94. Excellent work and very well presented! You are a credit to your species.

    ReplyDelete
  95. You should consider revisiting the Haversine equation because the Law of Cosines version that you're using is considered UNRELIABLE and NOT RECOMMENDED. See here for discussion.

    The haversine formula for decimal degree coordinates in Excel is:

    =2*asin(min(1,sqrt(sin((radians(lat2)-radians(lat1))/2)^2+cos(radians(lat1))*cos(radians(lat2))*sin((radians(lon2)-radians(lon1))/2))^2))*6371008.7714


    6371008.7714 m (3958.761316 miles) is the mean radius of Earth as defined by the International Union of Geodesy and Geophysics (IUGG).

    This haversine version will alwyas produce valid results. Your final equation produces "#NUM!" with, e.g., lat1: 41.89028, lon1: -87.75483, lat2: 41.89028, lon2: -87.75483.

    ReplyDelete
  96. Apologies, I could have simplified that just a bit:

    =2*asin(min(1,sqrt(sin((radians(lat2-lat1))/2)^2+cos(radians(lat1))*cos(radians(lat2))*sin((radians(lon2-lon1))/2))^2))*6371008.7714


    However, if we're going to worry about such a degree of false precision, all coordinates should really be converted from geodetic to geocentric before using any of these trigonometric equations.

    ReplyDelete
  97. Thanks. I was going to have to get on Google Earth and use the ruler tool to calculate distances from a central point to a number of weather stations that did have reported lat-long info. Your equation saved me a bunch of time.
    How would I reference your work in a paper citation?
    Thanks again,
    Joe H

    ReplyDelete
  98. Thanks Mate....appreciate your generosity.

    ReplyDelete
  99. Fantastic, works a treat. Thank you.

    ReplyDelete
  100. if the lat long values are in degrees, then cant we convert it into radians and then apply haversine's formula?

    ReplyDelete
  101. Thank you so much. supper ...

    ReplyDelete
  102. Wow thank you man. I compares the results to google maps and its within the same proximities. Thank you thank you.

    ReplyDelete
  103. Damn, I keep getting the #NUM error and can't figure out what's wrong...

    ReplyDelete
  104. Damn, I keep getting the #NUM error and can't figure out what's wrong...

    ReplyDelete
  105. Is this the greater circle line distance formula that you are using?

    ReplyDelete
  106. is this the greater circle line distance formula that you are using

    ReplyDelete
  107. This part of the formula keeps giving me errors- *COS(RADIANS(B2-B3))

    my longitude figures are all negative. I suspect that is the problem but unsure of what to do.

    ReplyDelete
  108. I am getting errors and I suspect it's with this part of the formula

    *COS(RADIANS(B2-B3))

    my longitude values are all negative. Do they need to be converted to positive to use? thanks

    ReplyDelete
  109. I copied it, pasted it, substituted my cell references for the lat long names, and I was done! It was too easy.
    Thanks!
    Steve

    ReplyDelete
  110. My longitude data is negative and causes this to bomb. Does it need to be made positive?

    ReplyDelete
  111. Thanks a lot, appreciate it very much :)

    Suresh

    ReplyDelete
  112. I'm pretty sure ppl will thank u 4ever, but I couldn't just use ur info and leave! so here is my THANK YOU as well!

    ReplyDelete
  113. Great! Thank you very much!

    ReplyDelete
  114. Awesome! I needed to get to feet but had to start somewhere! Just to continue from km to ft take result through =DistKm*3280.8

    ReplyDelete
  115. This is great - exactly what I was looking for.

    Just wondering if anyone has been able to build a solution that can take a few hundred points and cluster them based on certain criteria example within 10km of each other?

    ReplyDelete
  116. thank you for your sharing.

    ReplyDelete
  117. I searched for this solution for YEARS.......almost no one has ever put this out to where the average non-mathematician guy can obtain and use it. Thank You.

    This kind of math is DIFFICULT and not apparent to mere mortals.

    ReplyDelete
  118. Dude, this is awesome. Saved $59 purchase...

    ReplyDelete
  119. Still useful in 2013. Saved lots of time for me too. Thanks from the UK.

    ReplyDelete
  120. First of all, Many thanks for sharing this information. I am evaluating a product and I wanted to make sure that the samples are taken in a proper distances..... You saved my time ..I Can do it now ...
    Thanks from Canada :)

    ReplyDelete
  121. This is great. But if you want greater accuracy without having implement the Vincenty formula from scratch, you can simply adjust your radius according to the the formula at Wikipedia. I did this, and came up with the following formula that works in Google Spreadsheet (and probably Excel too):


    =ACOS(COS(RADIANS(90-dLat1*COS(RADIANS(90-dLat2))+SIN(RADIANS(90-dLat1))*SIN(RADIANS(90-dLat2))*COS(RADIANS(dLon1-dLon2))) * SQRT(((6378.135^2*cos(((dLat1+dLat2)/2)*(PI()/180)))^2+(6356.752^2*sin(((dLat1+dLat2)/2)*(PI()/180)))^2)/((6378.135*cos(((dLat1+dLat2)/2)*(PI()/180)))^2+(6356.750*sin(((dLat1+dLat2)/2)*(PI()/180)))^2))

    Alas, I can't make this more readable with PRE or TT tags, but you can cut and paste it into something with a fixed-width font to try to read it. :)

    ReplyDelete
  122. Very glad I came across this solution. Had not caught the mean vs max radius throwing off my calculations. Thanks!

    ReplyDelete
  123. First off, thank you very much. This helped. Hope I can give back a bit by suggesting a small improvement.

    When you go from your initial formula

    =ACOS(SIN(Lat1)*SIN(Lat2) +COS(Lat1)*COS(Lat2)*COS(Lon2-Lon1)) *6371

    to the one that takes input in degrees

    =RadiusEarth*ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2)))

    you also swap sin and cos in a few places. Not sure why you do this. You're basically just exploiting

    COS(RADIANS(90-x) == SIN(RADIANS(x))

    So, you could write your formula as

    =RadiusEarth*ACOS(SIN(RADIANS(Lat1)) *SIN(RADIANS(Lat2)) +COS(RADIANS(Lat1)) *COS(RADIANS(Lat2)) *COS(RADIANS(Long1-Long2)))

    Hope this helps.

    ReplyDelete
  124. You did a great job at making this post very detailed. I want to say thank you! You even supplied the miles and nautical miles needed!

    ReplyDelete
  125. Thank you so so much! This has helped my work so so much!!

    C

    ReplyDelete
  126. thank you!! just what i needed to demonstrate to a client, before we implement it in code.
    on the first search result.
    live long and prosper!829

    ReplyDelete
  127. Mate, you are awesome. I was tearing my hair out trying to make that atan2 thing work and here it is: one formula, in Excel.
    The Gods smile on you.

    ReplyDelete
  128. I have a distance calculator but it calc's "nearest" based on sets of l/l inputs so this one did the trick since I needed point to point calculator. Very nice, very easy. As others have said, still relavant in '13.

    ReplyDelete
  129. There is still something wrong in there. If you do the calculation using the same lat and long for lat1 and lat2 and for long1 and long 2, the distance comes out as 6.777km instead of 0km

    ReplyDelete
  130. There is still something wrong in there. Pluug in the same lat and long and instead of 0km, you get 6.777km

    ReplyDelete
  131. Thanks a lot for the formula!
    I have adapted it to take input from DDMMSS and converting them into DD.
    I checked a few points using some web calculators and seem to be off a few hundred meters. Has anyone experienced this issue?

    ReplyDelete
  132. thank you so much! i'm so glad God brought me to this!

    ReplyDelete
  133. Thanks for your formula

    ReplyDelete
  134. GOD BLESS YOU, GOOD MAN!!!!!

    ReplyDelete
  135. It works very well,
    ta.

    ReplyDelete
  136. Thanks a lot for taking the time to create this page, you've saved myself, and clearly a lot of other people, a huge amount of time and effort.
    Appreciative from South Africa.

    ReplyDelete
  137. woah! Thats so cool!
    Thanks so much!

    ReplyDelete
  138. Party on Wayne!!! Thank you!

    ReplyDelete
  139. Fantastic BlueMM! Saved me so much time I wasn't looking forward deconstructing equations back into excel code. Appreciate the post ...it works perfectly!

    Also I noticed someone suggested batchgeo for Geo-coding (Lats and Longs)apparently Google pulled them up for sharing that info so they had to pull it down.

    I found this site (http://www.gpsvisualizer.com/geocoder/ ) that allows up to 1000 geo-codes at a time and outputs as a CSV... apply BlueMM's formula and you can do all sorts of fancy things. Thanks so much :)

    ReplyDelete
  140. Wow! You just saved me a lot of work! Thank you simply does not suffice. :o)

    ReplyDelete
  141. Awesome, saved a good while trying to convert some of the other highly ranked posts into excel, alsop uses the formula correctly



    ReplyDelete
  142. Thanks a million!
    Greetings from Warsaw, Poland.

    ReplyDelete
  143. hi

    I have a spread sheet that has Lat / Lon in three cells each; A1=Lat Degrees; B1 = Lat Minutes; C1 = Lat Seconds (4 decimal places) and D1 = Lon Degrees; E1 = Lon Minutes; F1 = Lon Seconds (4 decimal places).

    Now, I will have cells h1 through L1 set the same way. H1 through L1 is my location where A1 through E1 is from a different location.

    I want N1 to show range in miles and O1 to show degrees from my location to the other location.

    I want to use the "Earth" co-ordinance NAD27.

    I tried some formulas I saw on the internet but they did not work. They had something about "time" method.

    I use DMS.ssssss only and I do not use meters, which confuses me.

    Can some one help?

    Contact me at kb8qeu AT gmail DOT com.

    Thanks in advance

    Ed --- KB8QEU

    OH BTW I use excel 4.0 because excel 9 keeps closing automatically. I did have this working on Lotus 123 in the early 90s but can't remember how.

    ReplyDelete
  144. This post continues to help people even though its really old; you have saved a lot of time for a lot of people.
    Thank you for your work.

    ReplyDelete
  145. thanks dude. still works in 2013!!

    ReplyDelete
  146. Really useful, many many thanks!

    ReplyDelete
  147. Thanks a lot it is of great Help.....was searching a lot and ultimate answer by u......Thnks RIT

    ReplyDelete
  148. I'm gettint #NAME-error. Any tips? Im using excel with OS X.

    ReplyDelete
  149. Thank you very much. It worked perfect. I set '=ACOS(COS(RADIANS(90-A2)) *COS(RADIANS(90-A3)) +SIN(RADIANS(90-A2)) *SIN(RADIANS(90-A3)) *COS(RADIANS(B2-B3))) *6371
    A2 = Lat1
    A3 = Lat2
    B2 = Lon1
    B3 = Lon2

    ReplyDelete
  150. thanks! helped out a lot ! :)

    ReplyDelete
  151. Thank you from Alaska! This saved a lot of time and made my project manageable.

    ReplyDelete
  152. I would really appreciate knowing how I can cite you and your work for my thesis - your equation (as others have said) saved me so much time and effort. Please let me know how to provide due credit! (another biologist tracking life distances)

    ReplyDelete
  153. GPS tracks with holes due to GPS reception loss are my biggest enemy in my quest to GPS tag travel photos. JGPSTrackEdit and Excel with your formula are my answer to creating missing track points with reasonable time stamps. Thanks for sharing.

    ReplyDelete
  154. Immensely helpful. Just saved me a good hour of work in converting PHP code I have which does the same job into Excel. Thank you!

    ReplyDelete
  155. Many thank to you man.... U have saved my time....

    ReplyDelete
  156. Thanks a lot. You surely made my job easy. Thanks.

    ReplyDelete
  157. Thank you!!! So helpful!!!

    ReplyDelete
  158. Cracking! Thank you for such a neat solution.

    ReplyDelete
  159. thanks a lot friend...you saved my one day of work else had to calculate distance for odd 250 links on google earth...thanks once again

    ReplyDelete
  160. Thank you! You are a life saver. I need to calculate distances between two points for a pricing template and this will do the trick.

    ReplyDelete
  161. thanks a lot; you made our lives so much easier

    ReplyDelete
  162. Very helpful stuff indeed. Many thanks.

    ReplyDelete