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).

163 comments:

dan said...

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

Anonymous said...

Simply... Thank you

Anonymous said...

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

Scott said...

Bless you.

Anonymous said...

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

Anonymous said...

Thank you!!!

Anonymous said...

You made my day!
Thanks a lot!

From a happy Norwegian

Anonymous said...

Thanks so much, it saved me hours.

Anonymous said...

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

Anonymous said...

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

Thomas said...

Amazing! just what I was looking for!

Eubian said...

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

Thanks very much.

Anonymous said...

Thanks a million!!!

PAULIN said...

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!

Anonymous said...

Absolutely wonderful!

Anonymous said...

Absolutely wonderful

Anonymous said...

Excellent, just excellent. Thanks very, very much.

dgillz said...

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.

Anonymous said...

that is so so frickin cool. thank you

John Noble said...

This is the Bomb!!!

Diane said...

You've saved me so much effort - thanks!

Anonymous said...

Thank you..Very Simply

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

Anonymous said...

U be 'da man!!!

Brad said...

You are a life saver!

Anonymous said...

simply amazing..thx

Anonymous said...

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

Zach said...

You are a life saver.

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

Ravi said...

Thanks a lot. Saved a lot of time.

Anonymous said...

Awesome ! Great formula and even better description.

Anonymous said...

Yep. thanks!

dr mike said...

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

Anonymous said...

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

Anonymous said...

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

Anonymous said...

amazing!

Ben said...

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

Anonymous said...

this is great-can it be tweaked into Crystal?

Norm Feaster said...

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

Crystalfan said...

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

Anonymous said...

First Thanks in 2010. Thanks!

Crystalfan said...

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

Anonymous said...

Thanks a lot. Saved loads of effort

Anonymous said...

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!

Anonymous said...

Awesom!!!

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

Jennifer said...

Thank you. You saved me so SO much work.

Anonymous said...

Brilliant...simply brilliant...

sumesh22 said...

Awesome

Anonymous said...

You need to be kissed.

dave said...

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

Jocelyn said...

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

Anonymous said...

kick-ass man.

Universal Insights said...

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.

Anonymous said...

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

Wolfe said...

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.

Anonymous said...

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

Anonymous said...

Amazaing work.... Hats off mate

Anonymous said...

Amazing work.... Hats off mate

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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

John Chapman said...

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.

Anonymous said...

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!!!

Chris Gilbert said...

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

Anonymous said...

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

albatross said...

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.

Lucariell' said...

amazing!

anthony said...

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

Thanks Anthony fba@fbatm.biz

anthony said...

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

Anonymous said...

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

Paul said...

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

Thank you!

Anonymous said...

Thanks a lot.

Anonymous said...

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

leonil said...

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

Anonymous said...

Thanks a billion!

Owz said...

Good work! Many thanks from Wales!

Anonymous said...

This worked great! Thanks from Ontario!

Anonymous said...

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???

Anonymous said...

Thanks Man. "CHOKRAN" :)

Anonymous said...

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

Anonymous said...

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

rohnerfelix said...

seriously, you are the man!!

Traceyr said...

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

Anonymous said...

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

Jack said...

Thank you so much, this is exactly what i needed

Anonymous said...

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

Johs said...

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 !

Johs said...

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

Anonymous said...

you are heaven-sent! :) thanks man.

Anonymous said...

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.

Anonymous said...

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

Anonymous said...

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

great regard :)
engkin
eloknegerikuindonesia

Leslie said...

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.

Anonymous said...

Genius. Thank you.

Anonymous said...

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

Anonymous said...

Awesome...

Anonymous said...

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

you are a genius among men.

Will at Youthworks said...

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

Formula for Distance said...

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

sakhasiddha said...

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

Anonymous said...

Thanks brother! This is exactly what I was looking for!

Anonymous said...

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

God bless you

Anonymous said...

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

Anonymous said...

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

m3_07 said...

Thanks Mate....appreciate your generosity.

Anonymous said...

Fantastic, works a treat. Thank you.

Anonymous said...

Thank you so much. supper ...

Marrin said...

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

Cigeria said...

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

Protonus said...

Thank you!

Anonymous said...

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

Suresh said...

Thanks a lot, appreciate it very much :)

Suresh

Suresh said...

Thanks a lot, it helped me.
-suresh

Unknown said...

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!

Anonymous said...

THANKS!!!

Anonymous said...

Great! Thank you very much!

Anonymous said...

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

n1y0 said...

Thank you!

n1y0 said...

Thank you!

Jean-Philippe Hernandiz said...

Holly ship! you made my day!

map_maker said...

Thank you, thank you, thank you, thank you.

Like the others have said, you just saved me a ton of time (that I didn't have).

Cheers from Germany!

Anonymous said...

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?

Anonymous said...

thank you for your sharing.

Anonymous said...

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.

Anonymous said...

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

Anonymous said...

Thank you!!

Anonymous said...

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

Anonymous said...

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 :)

Jyotirmoy Dalal said...

great help ! thanks a lot!!!

Ben said...

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

Erik Hovland said...

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!

Anonymous said...

Thank you so so much! This has helped my work so so much!!

C

Anonymous said...

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

Anonymous said...

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.

rb said...

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.

Radar said...

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

Anonymous said...

Thanks for your formula

Dmitry said...

GOD BLESS YOU, GOOD MAN!!!!!

Anonymous said...

It works very well,
ta.

Anonymous said...

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.

Anonymous said...

woah! Thats so cool!
Thanks so much!

Anonymous said...

Party on Wayne!!! Thank you!

Kenny said...

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

Anonymous said...

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



Anonymous said...

Thanks a million!
Greetings from Warsaw, Poland.

Anonymous said...

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.

Robert Yerex said...

Saved me a couple of hours!

Thanks

Davo said...

thanks dude. still works in 2013!!

Anonymous said...

Really useful, many many thanks!

Anonymous said...

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

Anonymous said...

Fantastic!!!!!!!!!!

Anonymous said...

thanks! helped out a lot ! :)

Anonymous said...

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

Anonymous said...

Incredible. Thanks.

Anonymous said...

Thanks a ton

Aneesh Hiremath said...

Thanks a lot! Saved us a lot of pain.

Al T said...

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.

Wireless Waffle said...

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!

Anonymous said...

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

Anonymous said...

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

JTT said...

Brilliant.

Anonymous said...

Thank you!!! So helpful!!!