Latitude, Longitude,
And Great Circles

This page describes how to work with Latitude and Longitude in Excel and how to compute Great Circle Distances.

Introduction

You can use Excel's trigonometric and time functions to work with Latitude and Longitude values. There are two different ways express geographical coordinates. The first is to use a time format of degrees:minutes:seconds. This is the same format as an Excel time value. The other method is to use decimal degrees, representing values as the number of degrees as a real number. For example, the location 20 degrees, 30 minutes, 40 seconds can be represented as the time 20:30:40 or in decimal degrees as 20.51111.

You can download the file with all the example formulas and code on this page.

Hemispheres And Positive And Negative Coordinates

If you are restricting yourself to one hemisphere for latitude and one hemisphere for longitude, you can use either time format or decimal degrees format and use postive values for both. If you are working with two hemispheres, you must choose one hemisphere to be positive and then use negative values for locations in the other hemisphere. This is fine for decimal degree values, but Excel can't display negative time values. Therefore, if you are working in two hemispheres, you need to use the decimal degrees format or set the date system to the 1904 base. To change the date format, go to the Tools menu, choose Options and then the Calculation tab. There, check the 1904 date system setting. When using positive and negative values to specify the hemisphere, it doesn't matter which hemisphere is assigned to positive numbers and which is assigned to negative numbers. However, you must be consistent in all your calculations. If North is positive in one set of coordinates, it must be positive in all coordinates.

Number Formatting For Latitude And Longitude

If you are using the time format for latitude and longitude values, you will need to change the number format for those cells. By default, when you enter a time in a cell, Excel will use the Short Time format as specified in the Windows Regional Settings. Excel will "roll-over" times at 24 hours (although the underlying value remains correct). For example, the time value 30:40:50 is displayed as 06:40:50. To correct this, you can use a number format of [hh]:mm:ss. The square brackets [ ] instruct Excel not to roll over the displayed value as a "time of day" value.

Also, you can instead use a custom number format to display time-format latitude and longitude values with a cartographic format. Select the cells that you want to format, go to the Format menu, choose Cells and then the Number tab. There, choose Custom in the Category list and enter [hh]º mm' ss\" in the Type text box. To enter the degree symbox (º), hold down the ALT key and press 0176 on the numeric keypad on the right side of the keyboard (do not use the numbers above the letters). This number format will display the time formatted coordinate value 20:30:40, for example, as 20° 30' 40". Note, though, that even though the cell is formated to display degrees, minutes, and seconds, you must enter the number as a time using the ":" character -- e.g., 20:30:40.

Converting Between Time Format Coordinates And Decimal Degrees

Since time formatted coordinates are just numbers representing the fraction of a 24-hour day (06:00:00 = 0.25, 12:00:00 = 0.5, 18:00:00 = 0.75, etc.), you can multiply a time format coordinate by 24 to get the decimal degrees value. For example, if cell A1 has the value 20:30:40, you can use =A1*24 to get the decimal degrees result, in this case 20.511111. Similarly, you can convert a decimal degree value to time format by dividing by 24. For example, =B1/24. Be sure to format the cells with a number format of [hh]:mm:ss or [hh]º mm' ss\".

Determining Degrees, Minutes, And Seconds

If you have coordinate values in time format, you can use the following formulas to extract the degree, minute, and second values. In all these examples, the time format value is assumed to be in cell A1.


=INT(A1) returns degrees
=MINUTE(A1) returns minutes
=SECOND(A1) returns seconds.

For decimal degrees values, use the formulas:

=INT(A1) returns degrees
=MINUTE((A1-INT(A1))/24) returns minutes
=SECOND((A1-INT(A1))/24 returns seconds.

In addition to the formulas above, you can use an array formula to split out the degree, minute, and second values from either a time-format value or a decimal degree value. To split out the component from a time-format value, select the three cells that will get the component values and enter the following array formula:

={1,0,0}*INT(A1*24)+{0,1,0}*MINUTE(A1)+{0,0,1}*SECOND(A1)

and press CTRL SHIFT ENTER. For more information about array formulas, see the array formulas page on this web site. In the formula above, note the differences between the parentheses and the curly braces { }.

To break out the degrees, minutes, and seconds values from a decimal degrees value, use the following array formula. Select the cells that will get the component values, type the formula, and press CTRL SHIFT ENTER.

={1,0,0}*INT(A1)+{0,1,0}*MINUTE(A1/24)+{0,0,1}*SECOND(A1/24)

As before, note the difference between the parentheses and the curly braces.

Great Circle Distances

In Mathematics and Cartography, a Great Circle Distance is the shortest path between two points on the surface of a sphere (and we will assume that the Earth is a perfect sphere, even though it really isn't). This is the "as the crow files" distance between the two points. All lines of Longitude are Great Circles, while the Equator is the only Latitudinal Great Circle.

To calculate the Great Circle Distance between points, we first calculate the spherical central angle between the two points and then multiply that angle (in Radians) by the radius of the Earth. Don't worry if you don't know what spherical central angle means -- the formulas will calculate it for you. The equation used in the formulas is



Note that Excel's trigonometric functions work only with Radians, not Degrees, so the coordinate values must be converted from Degrees to Radians. The RADIANS function is used to convert Degrees to Radians.

Great Circle Distances With Time Format Coordinates

To calculate the Great Circle Distance between Location1 and Location2, use the formula:

=RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(D49*24)-RADIANS(D50*24))/2)^2)
+COS(RADIANS(D49*24))*COS(RADIANS(D50*24))*
(SIN((RADIANS(E49*24)-RADIANS(E50*24))/2)^2)))))

In this example, the RadiusEarth is a named cell with the value 6370.97327862273 for nautical kilometers or 3958.73926185 for nautical miles. Multiply either value by 1.1507794480 to get the length in statute kilometers or statute miles. Cells D49 and E49 are the latitude and longitude of Location 1 and cells D50 and E50 are the latitude and longitude of Location 2. These coordinates are in Time format. The result is the distance in either miles or kilometers, depending on the value in the cell named RadiusEarth. For readability, the formula is broken into several lines. In Excel, of course, the formula is in a single line in a single cell.

The same formula, using defined names for coordinates is shown below. Lat1_ and Long1_ are the coordinates of the first location. Lat2_ and Long2_ are the coordinates of the second location.

=RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(Lat1_*24)-
RADIANS(Lat2_*24))/2)^2)+COS(RADIANS(Lat1_*24))*COS(RADIANS(Lat2_*24))*
(SIN((RADIANS(Long1_*24)-RADIANS(Long2_*24))/2)^2)))))

Great Circle Distances With Decimal Degrees Coordinates

To calculate the Great Circle Distance between Location 1 and Location 2, where the coordinates are in decimal degree format, use the following formula:

=RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(D71)-RADIANS(D72))/2)^2)+
COS(RADIANS(D71))*COS(RADIANS(D72))*
(SIN((RADIANS(E71)-RADIANS(E72))/2)^2)))))

Here, cells D71 and E71 contain the latitude and longitude of Location 1 and cells D72 and E72 contain the latitude and longitude of Location 2. For readability, the formula is split over several lines. In Excel, of course, the formula is in a single line in a single cell.

The same formula, using defined names for the coordinates is shown below. Lat1__ and Long1__ are the coordinates of the start location and Lat2__ and Long2__ are the coordinates of the end location.

=RadiusEarth*((2*ASIN(SQRT((SIN((RADIANS(Lat1__)-RADIANS(Lat2__))/2)^2)+
COS(RADIANS(Lat1__))*COS(RADIANS(Lat2__))* (SIN((RADIANS(Long1__)-RADIANS(Long2__))/2)^2)))))

Great Circle Distances In VBA

The following VBA code will calculate a Great Circle Distance in either Kilometers or Miles (as specified in the ResultAsMiles parameter -- True indicates Miles, False indicates Kilometers). The inputs may be either decimal degrees or time-format values. (Set the ValuesAsDecimalDegrees parameter to True for decimal degrees, False for Time degrees.)

Private Const C_RADIUS_EARTH_KM As Double = 6370.97327862
Private Const C_RADIUS_EARTH_MI As Double = 3958.73926185
Private Const C_PI As Double = 3.14159265358979

Function GreatCircleDistance(Latitude1 As Double, Longitude1 As Double, _
            Latitude2 As Double, Longitude2 As Double, _
            ValuesAsDecimalDegrees As Boolean, _
            ResultAsMiles As Boolean) As Double

Dim Lat1 As Double
Dim Lat2 As Double
Dim Long1 As Double
Dim Long2 As Double
Dim X As Long
Dim Delta As Double

If ValuesAsDecimalDegrees = True Then
    X = 1
Else
    X = 24
End If

' convert to decimal degrees
Lat1 = Latitude1 * X
Long1 = Longitude1 * X
Lat2 = Latitude2 * X
Long2 = Longitude2 * X

' convert to radians: radians = (degrees/180) * PI
Lat1 = (Lat1 / 180) * C_PI
Lat2 = (Lat2 / 180) * C_PI
Long1 = (Long1 / 180) * C_PI
Long2 = (Long2 / 180) * C_PI

' get the central spherical angle
Delta = ((2 * ArcSin(Sqr((Sin((Lat1 - Lat2) / 2) ^ 2) + _
    Cos(Lat1) * Cos(Lat2) * (Sin((Long1 - Long2) / 2) ^ 2)))))
    
If ResultAsMiles = True Then
    GreatCircleDistance = Delta * C_RADIUS_EARTH_MI
Else
    GreatCircleDistance = Delta * C_RADIUS_EARTH_KM
End If

End Function

Function ArcSin(X As Double) As Double
    ' VBA doesn't have an ArcSin function. Improvise.
    ArcSin = Atn(X / Sqr(-X * X + 1))
End Function

Great Circle Bearings

You can calculate the initial and terminal bearing of a great circle with formulas. The initial bearing is the bearing at which one would leave the starting point to travel on a Great Circle route to the end point. The initial bearing is given by the formula:

=DEGREES(IMARGUMENT(COMPLEX(North1-North2,East1-East2))+PI())

The terminal bearing, the bearing one will be travelling upon arrival at the end point, is give by the formula:

=DEGREES(IMARGUMENT(COMPLEX(North2-North1,East2-East1))+PI()).

Bearings are measured clockwise from North, with North as 0 degrees, East at 90 degrees, South at 180 degrees, and West at 270 degrees.

You can download the file with all the example formulas and code on this page.

This page last updated: 15-July-2011.