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:
«Oldest ‹Older 201 – 339 of 339Excellent, I need to adapt to get it to work in Qlikview
Your excel formula has very worked well for me too. THANK YOU.
I have also been looking for the excel formulas for determining (1) the bearing between the two positions, and (2) a second position from the first position + heading w/distance.
I found several references to these formulas on the web but I am unable to get them work properly. Any assistance greatly appreciated.
Mark W
is the answer to formula in nautical miles or regular miles???
After examining the formulas, I am thinking it is solving in nautical miles???
Can anyone help ?
LOL, I don't understand a thing you wrote - this is way beyond my training - but I plugged in your formula and it worked, so thank you!
guys can someone tell me wat does radians mean
and also tell me wat does Acos mean and how these should be calculated
Thanks!
This is just an incredible time saver. Thank you very much!
You are the man!
Thanks man, really useful..I appreciate this very much..
-meIndonesia
Thanks!
Thank you. Most practical solution I have found.
BRILLIANT!!! THANKS A LOT!
Could someone explain how this actually works. I know why we conver to radians but not sure what the ACOS, SIN and COS functions are doing?
So I guess this has been said more than a few times above, but thank you again for posting this.
awesome!!
Thank you very much=)
How about an excel formula to give ending lat and long coordinates from a given beginning lat, long, azimuth and distance in miles
God bless u for this.wowwow.thank u so much
Is there a way to do multiple locations like 1000 places and find which is the nearest?
I assume triangulate but how?
Thanks
This is a very helpful tool.
Okofo
Thank you so
Wow...thank you so much for this. You saved us SOOOO much time and energy. Cheers! God Bless
Thank you for making this available. It is a significant help for geo-analysis.
-Mike H.
A cool site for those of you wanting to calculate your journey between two places www.DistanceCalculator.co.za
thanks for size idea jus for GPS. .realy good!!!
Big THANKS!!
Thanks, have used this in a libreoffice spreadsheet - no changes needed :)
AMAZING!!!! AMAZING!!! AMAZING!!!
Awesome. You are the man!!!
Excellent!
Works perfectly fine with PostgreSQL, too.
Thank you very much!
Somebody help me pls!
I have these coordinates:
47,49760 19,05395419
47,46597137 19,04359477
but in my excel the formula doesn't work..anybody?
Thx, this is very helful.
fantastic. Thank you. It doesn't seem to differentiate between E/W coordinates as well as N/S. Slight error when running coordinates across the equator
After much internet research your walk through was the absolute BEST! Thanks for the help
Hello,
I tried that calculation in order to find the distance between two points. I converted my latitudes and longitudes into radians but I take very large values (e.g. 9726 Km instead of 150 Km). Are there any suggestions?
Moreover, why is that subtraction of lats and lons from 90?
Hello,
I tried to use this relation but I didn't take any reasonable results (e.g. I took 9730 Km instead of 150 Km). Are there any suggestions?
Why is that subtraction between lats and lons with 90?
I tried this subtraction and I tried radian conversion as well but it didn't work.
Thank you,
Stavros
Excellenté
2015 and the thank yous are still coming. This one from Ireland. Thank you, thank you, thank you.
I'll like to add my thanks also, you cut my workload by a huge amount.
WOW, THIS IS AMAZING. You are a rockstar! Thank you
Thank you a lot! Just what I needed and works perfectly (validated on google maps).
Greatings from Brazil.
Thank you so much!!! Very Usefull
Nice , but I am afraid it do not take in attention, south/north hemisphere and mabe as well east/west.
But anway this formula help me to short time, to develop my one.
Good job .
EXCELLENT - thank you
Nicely done sir, takes the output from a phone app, giving a 99.77% accuracy over 1300 readings :-)
This is great! Thanks!
Second time using this - firstly in January 2014 and now in Jan 16 used for vessels now using it for aircraft
Hi, Can you anyone please help what is in the cells A2, A3, B2 and B3.
i'm looking for distance between two zip codes.
Thanks,
Can anyone share sample excel file
Thank you so much for the share. Your time is greatly appreciated.
I apply your formula =ACOS(COS(RADIANS(90-A2)) *COS(RADIANS(90-A3)) +SIN(RADIANS(90-A2)) *SIN(RADIANS(90-A3)) *COS(RADIANS(B2-B3))) *6371
but don't seem to get the same results as I do in Bing maps.In one case I am seeing 12.22km on excel and 22.1km in Bing maps. Am I doing something wrong. Sincerely. Peter
Thank you! :-)
Thank you for this helpful post!
Thank you. This is most excellent!
Thanks for the tips! It's very helpful. Looking forward to read more from you.
Thank you so much Sir, GOD BLESS YOU.
Great! Thank you a lot!
Still the only working formula on the internet!
Thank you so much.
So useful! Thanks now in 2016!
that was awesome. my assignment was to calculate the distance between points and by applying the GIS and drawing the poly-line then measure the poly line length. but it make the easy wasy. this distance is in simple mile????
Thanks a billion. You shall continue to grow in knowledge as i have learned this selfless attitude of sharing information to even people i know not from what you just did.
Totally awesome! Thank you!
Awesome !!!
Thank You.
OHM
Really awesome !!!
Thank you.
OHM
You are an absolute genius and saved me a lot of work. Cheers from Australia.
Amazing!
Excellent! and SO helpful! THANK YOU!
From that formula I have created a ready-to-use Excel spreadsheet. It is presently on the web at:
http://bit.ly/1WKbHu2
Another thank you from me! This would have taken a lot of time, instead it was easy as eating pie!
Cheers from Chile!
I gotta say, I have probably used this page 20 times in the past couple months. Solid work. Thanks a ton.
Thank so much , you saved my time!!! elias
Thank so much, you saved much of my time!!
This is really helpful - thank you so much!
I don't care if this post is more than 2 years old. It's great. Thank you, sir.
You saved my day. Thanks a lot.
Jamal Zraqou.
i found this very helpful...but whats the significance of 'A' before 'cos' in the formular. =ACOS(COS(RADIANS(90-A2)) *COS(RADIANS(90-C2)) +SIN(RADIANS(90-A2)) *SIN(RADIANS(90-C2)) *COS(RADIANS(B2-D2))) *6371
The mile output appears to be too high - am I missing something? Thanks!
Thank you so much! This cost me a half a day of my life today!!
This is perfect!! Can I be cheeky and ask for a script in R for this?
This is perfect!! Can I be cheeky and ask for the R script for this?
I would love to join the praise BUT the formula only works for me for some of the distances I calculate - does anyone have an idea what's wrong? (e.g. it does not work for 12.99922, 48.967642 and 11.575529, 48.137039 - the formula gives me 182 km, but in reality, it is 139 km) - HELP, please!!!!
Thank you so much! That you make all of our work much more easier. However, I have one question that I really couldn't understand. Why does not the distance between some of the same two coordinates come out as exactly "0"? It comes out as "0,00009493" and "0,00013425". It would be really helpful if you or somebody else can answer this.
What do the variable A2, A3, B2, and B3 stand for?
I can be contacted at rryyaann50@gmail.com.....please help
What do the variable A2, A3, B2, and B3 stand for?
I can be contacted at rryyaann50@gmail.com......please help
GREATT!!
I sure appreciate your work on this!
Sick formula. Thanks!
Amazing!! Thank yoU!
What can I say except thanks!
I'll jump on now, just to keep the thanks flowing.
Saved my team a ton of time, and now I'm a hero. But really, you're the only hero I see!
2017!
Still helpful. Thanks man..
Worked perfect!
Awesome thanks!
Thanks a ton! Very useful.
Thanks loads! I noticed that this post was put up 10 years ago! Helping people since 2007!
Dude, legendary, this was so helpful - thanks!
thank you .....
Thank you very much , after lots of struggle and the find your blog.
Amazing!! thanks so much!
Brillian!
nice write up.Calculate area of any building with Google maps.
Thank you so much.
Thank you very much!!
Thanks a lot!
I love you
Just want to let you know that 10 years later, your work is still appreciated. Thanks for developing this simple tool and providing the discussion on radius.
Thanks man it work for me to create sheet for my client Model X
Great help
Thank you so so so much!
I'm really grateful for this.
But somehow I encountered some problems when I calculate the distance of a lat long point towards itself.
in my opinion (CMIIW), the distances should be equal to zero, since it reflects the distance towards itself. However, based on your final function the result return to >1 miles distances instead.
here is the function I attached to my worksheet,
=ACOS(COS(RADIANS(90-$C$9))*COS(RADIANS(C9)+SIN(RADIANS(90-$C$9))*SIN(RADIANS(90-C9))*COS(RADIANS($D$9-D9)*6371)))
can you explain to me why?
right oN!
Thanks, just what I need
Thanks, just what I need
doesn't the formula SQRT((X2-X1)^2+(Y2-Y1)^2) work same?
doesn't the formula SQRT((X2-X1)^2+(Y2-Y1)^2) work same?
Thanks
Superb stuff
Marvelous !!!!
Thanks a lot..
Thanks man! it is very helpful!
Cheers guys... years later this is still fab!
Note precise distance calculation on ellipsoid surface requires way more complicated approach. Vincenty formulae is one of the solutions. For Excel implementation see https://github.com/tdjastrzebski/VincentyExcel
Bless you!.
Saved my day.
Bless you!
You saved my day.
Thank you, this formula saved me so much work.
Mad props Sir!
Thank you, thank you! It's been over four years since the last comment (almost exactly 11 years since original post), but I agree with previous posts that I couldn't just use your info and leave! V cool how many people you've helped, and the info is still relevant now - granted I'm currently still on Office 2013 so...
Thank you...Really helpful...
To be thanked, to be commended.
Thanks for such a help
Great work thank you for the effort this is the only excel formula that I could find to work.
GENIUS!!!!! Exactly what I was looking for. THANKS!!!!
What format do you enter Lat / Long into cells and what format do you use for the cell (number, general, custom, etc) for your calculation to work? Thanks
Just Awesome!! You helped a lot..
One request....could you please share excel formula to calculate orientation between two points in degrees...??
Thanks in advance.... :)
Simply Brilliant. Thanks
10 years later, still a superstar!!!! thank you! :)
Thank you in 2019!!!
Maaaaan, it's 2019 and I'm from Brazil... But THANKS!!!!
2019 and this blog post is still relevant. Well done, you nailed it. Thanks!
This formula is accurate. Its result perfectly matches the direct site survey result. Thank you so much!
Life. Saver.
Muchísimas gracias !!! te escribo +10 años después de tu post y me resultó muy útil.
Miles Gracias por este post! + de 10 años después y me es de gran ayuda
You have my vote for president of america :)
Post a Comment