See how to calculate distance in Excel, using Latitude and Longitude, in this tutorial by Excel MVP, Jerry Latham.
Addresses to longitude and latitude 05:01 AM I have a list of customers and there own addresses, i was trying to use the map Visualization but i need to define the Latitude and Longitude, but they are a written addresses!! Get Longitude And Latitude Coordinates In Excel 2016 How To Plug This Get Longitude And Latitude Coordinates In Excel 2016 Free With 13 So just by one click of button right inside from MS Excel where all the addresses(no matter structured or unstructured) are mentioned, can also have geo codes (Latitude, Longitude) using APIs.
Getting Started
For those who are in a rush for the solution and don't need all the background information, jump to the longitude latitude code. Or download the calculation workbook, and enter your longitude and latitude.
The search for an accurate solution to this problem has led me to numerous sites and attempted solutions. A long list of related sites is included at the end of all of this, but the most crucial to what I've found to be the current end of the road are these:
along with
Accuracy is Important
A formula that is accepted to provide results that are accurate to within millimeters is known as Vincenty's formula. Naturally the accuracy of the results depends in large part on the accuracy of the latitude/longitude pairs describing the two points.
Why all the fuss over accuracy? Well, from what I've seen of other formulas, especially those written as a single worksheet function, their values differ quite a bit for what might be considered 'life critical' situations. Typically they are short by some number of meters, typically about 20 to 30 feet per statute mile, and after flying just 30 or 40 miles, I wouldn't care to land several hundred feet short of the approach end of a runway, much less be off by over 7 miles on a trip between Los Angeles and Honolulu.
Since the general tendency in dealing with these types of calculations is to 'measure it with a micrometer, mark it with chalk and cut it with an axe', what we have here is a very precise micrometer to begin the measuring process with.
Excel Formulas for Distance
There are numerous Excel worksheet functions that will return an initial heading from one point to the destination point for a Great Circle path between them and similar formulas to return the distance between them.
But based on experience using them and comparing them to known measured distances, the Vincenty method of calculating the distance between the points has not been translated into a single Excel worksheet function, and very likely cannot be at least not easily.
Because it relies on reiterative calculations to deal with points that are very close to being on the exact opposite sides of the world, implementing it as even a series of Excel worksheet formulas is a daunting task.
Creating Excel Code for Distance
The basis for the solution presented by T. Vincenty can be found here:
During my search I first found the movable-type.co.uk page which presented the coded solution in JavaScript. That didn't do me much good as far as coming up with an Excel VBA solution; so I continued the search and finally found the lost-species.livejournal.com page!!
I thought the search was over. Almost, but not quite.
When I moved that code into an Excel code module I was confronted with two sections that Excel not so politely informed me were 'too complex' for it to evaluate. I managed to break those down into simpler statements that Excel could deal with and that did not corrupt the final results. That is the sum total of my contribution to the function provided below. I claim nothing more than that small contribution.
Working With Angles in Excel
But before using the function, there are a few preliminary steps that must be considered. Most significant is that Excel and VB works with angles expressed in radians, not as decimal values of the angles, nor from their initial 'plain English' representation.
Consider this situation:
You have a Latitude represented as 10° 27' 36' S (10 degrees, 27 minutes 36 seconds South)
You need to get that into radians, and there is not a direct way to do it, before we can get it to radians it has to be converted into a decimal representation. We need to see it as: 10.46 which is the decimal equivalent to 10° 27' 36' and we need to take into consideration whether it is a North or South latitude, with South latitudes being treated as negative numbers.
Luckily Microsoft provides a couple of handy functions to convert standard angular notations to their decimal equivalent, and back, at this page:
Those routines are included at the code section and one of them has a change made by me to permit you to make a regular angle entry as
10~ 27' 36' S instead of 10° 27' 36' S
because ~ is directly accessible from the keyboard, while the ° is not.
Converting to Radians
After converting the standard notation to a decimal value, we still have to convert that to radians and deal with the sign of the radian result.
The routines and formulas here consider negative latitudes to be South latitudes and negative longitudes to be West longitudes. While this may seem unfair to those of us living in the western hemisphere, what can I say other than deal with it!?
A decimal degree value can be converted to radians in several ways in Excel and for this process, a simple function is used that is also included in the code presented later.
The basic formula is
radians = angleAsDecimal x (Pi / 180)
where Pi is 3.14159265358979
The Final Solution
Copy all of the code below and paste it into a regular code module in your workbook. Instructions for placing the code into a regular module are here: Copy Excel VBA Code to a Regular Module
Set up your worksheet to pass the latitudes and longitudes of the start and end points as standard entries, then enter a formula to pass them to function distVincenty().
Given two points with these coordinates:
Point 1:
- Latitude: 37° 57' 3.7203' S
- Longitude: 144° 25' 29.5244' E
Point 2:
- Latitude: 37° 39' 10.1561' S
- Longitude: 143° 55' 35.3839' E
The general format of the function call is:
=distVincenty( Pt1_LatAsDecimal, Pt1_LongAsDecimal, Pt2_LatAsDecimal, Pt2_LongAsDecimal)
A raw formula would look like this [Note the double-double quotes after the seconds entries]. The SignIt() function, provided as part of the code, converts a standard angular entry to signed decimal value.
=distVincenty(SignIt('37° 57' 3.7203' S '), SignIt('144° 25' 29.5244' E'), SignIt('37° 39' 10.1561' S'), SignIt('143° 55' 35.3839' E'))
You can use the ~ symbol instead of the ° symbol if it makes it easier for you:
=distVincenty(SignIt('37~ 57' 3.7203' S '), SignIt('144~ 25' 29.5244' E'), SignIt('37~ 39' 10.1561' S'), SignIt('143~ 55' 35.3839' E'))
If the coordinates for Point 1 are in B2 and C2 and the coordinates for Point 2 are in B3 and C3, then it could be entered as
=distVincenty(SignIt(B2), SignIt(C2), SignIt(B3), SignIt(C3))
The result for the 2 sample points used above should be 54972.271, and this result is in Meters.
Problems Encountered
The initial code that I began working with generated 'formula too complex' errors in two statements. These statements were initially broken into two pieces and then those two separate calculations were 'rejoined' in a formula to obtain their final result without the 'formula too complex' error.
During the preparation of this document, the package was tested in Excel 2010 64-bit and began returning #VALUE! errors for all input values. Investigation determined that a portion of the already split formula to determine the deltaSigma value was generating an overflow error. This error did not occur in the 32-bit version of Excel 2010, nor in Excel 2003 (a 32-bit application).
The offending line of code was once again broken down into smaller pieces that were eventually rejoined in a mathematically correct process that resulted in the proper values being determined without resorting to any alteration of the original algorithm at all.
These sections are noted in the comments in the code for Function distVincenty() below.
For any questions regarding all of this, contact Jerry Latham at HelpFrom@JLathamSite.com.
The Longitude Latitude Code
Now, at last, the code:
Download the Workbook
To see the code, and test the formulas, download the Excel Distance Calculation sample workbook. The file is in xlsm format, and is zipped. There are macros in the workbook, so enable the macros if you want to test the code.