Loading

Got a version of Excel that uses the
menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)?
**This site is for you!** If you
use a later version of Excel, visit
our *ExcelTips* site focusing on the ribbon interface.

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.

Learn more about Allen...

ExcelTips FAQ

Ask an Excel Question

Make a Comment

Free Business Forms

Free Calendars

** Please Note:** This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later),

Mike keeps track of a series of latitude and longitude values in an Excel worksheet. As these are essentially points on a grid, Mike would like to calculate the distance between any two given latitude/longitude pairs.

If the latitude and longitude pairs were really just points on a grid, then calculating the distance between them would be easy. The problem is that they are really points on a sphere, which means that you can't use flat-grid calculations to determine distance. In addition, there are many ways that you can calculate distances: shortest surface distance, optimum flight path ("as the crow flies"), distance through the earth, driving distance, etc.

Obviously this could be a complicated question. In the space available, I'll examine a couple of ways to determine the great circle distance ("as the crow flies"), and then provide some references for additional information on the other types of calculations.

The first thing you need to figure out is how the latitude and longitude of each point will be represented in Excel. There are several ways it could be represented. For instance, you could enter the degrees, minutes, and seconds in individual cells. Or, you could have them in a singe cell as DD:MM:SS. Either way is acceptable, but they will need to be treated differently your formulas. Why? Because if you enter latitude and longitude as DD:MM:SS, then Excel will convert them internally into a time value, and you just need to take that conversion into account.

What you are going to need to do, no matter what, is convert your latitude and longitude into a decimal value in radians. If you have a coordinate in three separate cells (degrees, minutes, and seconds), then you can use the following formula to do the conversion to a decimal value in radians:

=RADIANS((Degrees*3600+Minutes*60+Seconds)/3600)

The formula uses named ranges for your degrees, minutes, and seconds. It converts those three values into a single value representing total degrees, and then uses the RADIANS function to convert this to radians. If you start with a value of 32 degrees, 48 minutes, and 0 seconds, the formula ends up looking like this:

=RADIANS((32*3600+48*60+0)/3600) =RADIANS((115200+2880+0)/3600) =RADIANS(118080/3600) =RADIANS(32.8) =0.572467995

If you are storing your coordinates in the format of DD:MM:SS in a single cell (in this example, cell E12), then you can use the following formula to convert to a decimal value in radians:

=RADIANS((DAY(E12)*86400+HOUR(E12)*3600+MINUTE(E12)*60+SECOND(E12))/3600)

Assuming that cell E12 contains 32:48:00, then the formula ends up looking like this:

=RADIANS((1*86400+8*3600+48*60+0)/3600) =RADIANS((86400+28800+2880+0)/3600) =RADIANS(118080/3600) =RADIANS(32.8) =0.572467995

With your coordinates in radians, you can use a trigonometric formula to calculate distance along the surface of a sphere. There are many such formulas that could be used; the following formula will suffice for our purposes:

=ACOS(SIN(Lat1)*SIN(Lat2)+COS(Lat1)*COS(Lat2)*COS(Lon2-Lon1))*180/PI()*60

In this formula, each of the latitude (Lat1 and Lat2) and longitude (Lon1 and Lon2) coordinates must be a decimal value, in radians, as already discussed. The formula returns a value in nautical miles, which you can then apply various formulas to in order to convert to other units of measure, as desired.

You should realize that the values you come up with by using any formula that calculates distance on the surface of a sphere will give slightly erroneous results. Why? Because the Earth is not a perfect sphere. Thus, the distances should only be considered approximate. If you want to get a bit more accurate, then you can use the following formula to determine your nautical miles:

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

This formula substitutes the radius of the earth (3443.89849 nautical miles) for the radius of a sphere (180/PI()*60, or 3437.746771). Either way, the answer should still be considered approximate.

As you can tell, the formula to calculate distances is quite long. You may find it easier to develop your own user-defined function that will do the calculation for you. The following function takes four values (the two pairs of latitudes and longitudes, in degrees), and then returns a result in nautical miles:

Function CrowFlies(dlat1, dlon1, dlat2, dlon2) Pi = Application.Pi() earthradius = 3443.89849 'nautical miles lat1 = dlat1 * Pi / 180 lat2 = dlat2 * Pi / 180 lon1 = dlon1 * Pi / 180 lon2 = dlon2 * Pi / 180 cosX = Sin(lat1) * Sin(lat2) + Cos(lat1) _ * Cos(lat2) * Cos(lon1 - lon2) CrowFlies = earthradius * Application.Acos(cosX) End Function

If you would like to see a more in-depth discussion of latitudes and longitudes, and the math involved, you can find a good selection of articles at this site:

http://mathforum.org/library/drmath/sets/select/dm_lat_long.html

With the math under your belt, then you can start to look about at various formulas you can use. There is an interesting one in VBA at this Web page:

http://www.freevbcode.com/ShowCode.asp?ID=5532

A good general-purpose discussion can also be found at Chip Pearson's site, here:

http://www.cpearson.com/excel/LatLong.aspx

*ExcelTips* is your source for cost-effective Microsoft Excel training. This tip (3275) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Calculating the Distance between Points.

*Related Tips:*

**Program Successfully in Excel!** John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out *Excel 2013 Power Programming with VBA* today!

To answer my own question, I had the values in the wrong cells.

What am I doing wrong? Using the nautical formula, I enter the lat and long for the Empire State Building at 350 5th ave in NY, 40.7482871 and -73.9855716999999 and the lat and lon for 7 world trade center, 40.7131459 and -74.0119391 and I get a distance of 5064.4873. Mapquest gives a distance of 3.2 miles. I got the decimal values using stevemorse.org.

Regarding this topic, what would you recommend if I had a set of say 10,000 x,y coordinates and wanted for each one to calculate the distance of all the others. For example I am doing a study of all the different areas where there is 5 or more points within 500 meters of another point.

I know I can list all the x,y down, and all the x,y across the top and then calculate each one, but is there a better way?

I know I can list all the x,y down, and all the x,y across the top and then calculate each one, but is there a better way?

many thanks