This Page: www.cpearson.com/Excel/latlong.aspx

Last Updated: 06-Nov-2013

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 02-Jul-2017

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Search The Site:

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