Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Calculating the Distance between Points.

Calculating the Distance between Points

by Allen Wyatt
(last updated December 1, 2014)

9

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 (9512) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Calculating the Distance between Points.

Author Bio

Allen Wyatt

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

MORE FROM ALLEN

Limiting Precision

There may be times you need to limit the amount of precision Excel uses in its calculations. Here is one way to accomplish ...

Discover More

Losing Data in a Shared Workbook

When you create a shared workbook, you run the risk of losing some of the data in that workbook. Here's a discussion about ...

Discover More

Adjusting Date Values by Keypress

Excel allows you to store dates in a cell. Wouldn't it be great if you could select a cell containing a date and then ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Calculating the Interval between Occurrences

With a long list of items in a worksheet, you may want to determine the last time a particular item appeared in the list. ...

Discover More

Exact Formula Copies

When you copy a formula from one cell to another, Excel normally adjusts the cell references within the formula so they are ...

Discover More

Counting Records Matching Multiple Criteria

Excel provides worksheet functions that make it easy to count things. What if you want to count records that match more than ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 8?

2017-04-02 11:14:37

MIchael Armstrong

Last comment re the fig-things (I hope):

Both the square and curly brackets have to be included in the fig-thing, and a see-reference will be placed in the text where the fig-thing occurs. The image(s) will be placed at the end of the text with the labels supplied.

I have no idea where the lower-case "r"s came from when I included images. Would be really nice if I could edit or delete a comment. All in all, though, I like the new feature.


2017-04-02 11:11:11

MIchael Armstrong

OK, so both the square and curly brackets have to be included in the fig-thing, and the image(s) will be placed where the fig-things occur. I have no idea where the lower-case "r"s came from when I included images. Would be really nice if I could edit or delete a comment. All in all, though, I like the new feature.


2017-04-02 11:06:31

MIchael Armstrong

Let's try this add image thing again... looks like you need to include the square brackets, and do a separate "fig" for each image.r
As I understand @Catherine's problem, she has a bunch of points (xi,yi) in the plane, and a specific point (xc,yc) defined as the centre of pressure. In that case, the distance of each point is given as sqrt((xi-xc)2+(yi-yc)2) (you'll have to figure out the subscripts and superscripts). So, if all the x points are in Column A, and all the y points are in Column B, you can put the distances in Column C, and then find the average of those distances. In my example, I put the (xc,yc) point in Row 1, and 10 random points (xi,yi) in Rows 2-11. This seems pretty simple, so perhaps I've misunderstood your problem.r
(see Figure 1 below) r
(see Figure 2 below)

Figure 1. Formulas exposed

Figure 2. Results


2017-04-02 11:03:04

MIchael Armstrong

Let's try this add image thing again...r
As I understand @Catherine's problem, she has a bunch of points (xi,yi) in the plane, and a specific point (xc,yc) defined as the centre of pressure. In that case, the distance of each point is given as sqrt((xi-xc)2+(yi-yc)2) (you'll have to figure out the subscripts and superscripts). So, if all the x points are in Column A, and all the y points are in Column B, you can put the distances in Column C, and then find the average of those distances. In my example, I put the (xc,yc) point in Row 1, and 10 random points (xi,yi) in Rows 2-11. This seems pretty simple, so perhaps I've misunderstood your problem.r
(see Figure 1 below)

Figure 1. results


2017-04-02 10:59:59

MIchael Armstrong

As I understand @Catherine's problem, she has a bunch of points (xi,yi) in the plane, and a specific point (xc,yc) defined as the centre of pressure. In that case, the distance of each point is given as sqrt((xi-xc)2+(yi-yc)2) (you'll have to figure out the subscripts and superscripts). So, if all the x points are in Column A, and all the y points are in Column B, you can put the distances in Column C, and then find the average of those distances. In my example, I put the (xc,yc) point in Row 1, and 10 random points (xi,yi) in Rows 2-11. This seems pretty simple, so perhaps I've misunderstood your problem.
{fig}


2017-04-01 09:11:55

Cathrine Katsigianni

Dear Mr Wyatt
Thanks for the information about the calculations of distance in excel. I am a postgraduate student working on my dissertation which is related to biomechanics and have to calculate the average distance from the mean centre of pressure. To do this I first have to find the distance from the mean centre of pressure to each pair of points that represent the x and y axis(these are the coordinates related to the force platform). These points are form columns in excel under the letters(A, B, C etc). I would be grateful if you could let me know which formula I have to use to calculate these distances.
I look forward to hearing from you


2017-03-10 08:06:17

sandy

you are genius, its save my day.
When GPS is not available for short time, I need to find turns (left, right) using accelerometer(x,y,z), gyrometer value (x,y,z) ?


2016-03-29 09:09:18

shiva

i need excel sheet in reorder and day book sheets


2014-04-26 12:54:16

BillD

This article is a great spot to preach the advantages of using decimal degrees in all modern data sets rather than the ancient, error-prone and less intuitive use of minutes and seconds. Four or five decimal places is usually precise enough for most users.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.