Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.Challenge: I have two the latitude and longitude of two places. I want to know the distance between them, as the crow flies.
This took a lot of trial and error, made more tricky by having to also accommodate two points that straddled the equator. But the formula has been tested and works.
Put the latitude of point A (in degrees) in A1; its longitude in A2. And put the latitude of point B in B1; and its longitude in B2.
Below is the formula for the distance between the points.
=ROUNDDOWN(6371.0072*ACOS(COS(RADIANS(90-A1))*COS(RADIANS(90-B1))+SIN(RADIANS(90-A1))*SIN(RADIANS(90-B1))*COS(RADIANS(A2-B2))),1)
The 6371.0072 is the radius of the Earth, the first time I've ever used it in an Excel formula. The ROUNDDOWN function merely uses the "1" at the end of the formula to round it down to 0.1km in accuracy.
I've looked at at least a hundred Excel Haversine formulas - yours is the only I've found that actually works. Thank you so much.
Examples Please!
This has worked a treat for me!! Thank you so much for making it simple to use. Some of the posts I've seen elsewhere on this subject have baffled me, I got this sorted quickly and it's allowed me to calculate over 500k distances and make some important strategic decisions at work!!
This is the best formula