jump to navigation

How to Calculate Distance and Bearing to a Latitude Longitude Waypoint in Excel 04/30/2009

Posted by aliasmrjones in Uncategorized.

In testing code for Deathpod3000, I found it very valuable to have a quick and easy way to calculate correct distance and bearing to a latitude longitude waypoint to compare to results I was getting from my Atmega32 C code.  In this article, I will explain a little about the formulas, show them working in Excel and at the end of the article you can download a spreadsheet and play with them yourself.

For modern microprocessors with 8 byte IEEE floating point numbers, the spherical law of cosines can be used to compute distance between 2 latitude longitude points.  The law of cosines can be expressed in Excel as:


Whatever units you use for r, the radius of the earth,  will be the units of the returned value.  So, for example, if you want the returned distance in miles, you would use the value 3,959, the mean radius of the earth in miles.  You can also use 6,371 for kilometers or 20,903,520 for feet.  For Deathpod3000, since we are only dealing with small distances and need smallish scale, I used feet.   To play with in Excel, miles is nice because you can run some simple and easy sanity checks.

All of the latitude and longitude values in the formula must be in radians.  It is easy to convert degrees to radians with: =Degrees*PI()/180.  Substitute any cell for Degrees and you’ll get back radians.  Similarly, you can convert back from radians to degrees with:  =Radians*180/PI().  Substitute any cell for Radians to convert to degrees.

I created columns in my spreadsheet for Degrees and Radians since I’m used to dealing with latitude and longitude in degrees, but the formulas need radians.  I also created rows for each of the latitude and longitude values and a cell for the value r so I could easily change from miles to feet and back.  The result looks like this:

Latitude Longitude Degrees Radians Convert

Latitude Longitude Degrees Radians Convert

You can see one of the radians to degrees conversion formulas just above the spreadsheet.

OK, now we’re ready to do the distance calculation.  As I said before, 1 degree of latitude is 60 nautical miles or roughly 69 statute miles.  Let’s change lat2 to be 1 degree less than lat1 and make lon1 and lon2 the same.  This should be 2 points directly north/south of each other and about 69 miles apart.  Now, let’s put in the law of cosines distance formula.  You can see the formula just above the sheet.

Law of Cosines Distance

Law of Cosines Distance

69 miles!  Just what we were expecting.  So that works great for Excel, javascript etc.  For 8 bit microcontrollers with 4 byte floats, however, the formula breaks down for small distances.  The precision of 4 byte floats causes too much error.  There is a slightly more complex formula that does work well at small distances called the haversine formula.  It takes a bit more work, but we should get the same distance we got for the spherical law of cosines.  Here is the haversine formula:

r is the same radius of the earth.
dLat = lat2 – lat1
dLon = lon2 = lon1
a =  (sin(dlat/2))^2+cos(lat1)*cos(lat2)*(sin(dlon/2))^2
c=2*(atan2(sqrt(1-a), sqrt(a))  (Note that parameters are reversed from the “normal” atan2 function)
distance = r * c

So, let’s add each of these to the spreadsheet and see if we get the same distance value we got before.

Excel Haversine Formula Distance

Excel Haversine Formula Distance

Bingo!  The distance reported by the spherical law of cosines formula and the haversine formula are the same.  If you’re just using excel or writing in C# on a modern computer, you can use the spherical law of cosines formula and be done.  If you’re using something like an atmega8, atmega32, pic or similar, it takes a bit more work, but the haversine formula will be more accurate for the short distances you’ll probably be navigating so go with that.

Ok, so now we know how far away from our waypoint we are, but we don’t yet know what direction to head.  The formula for determining bearing to a waypoint is:

=MOD(ATAN2((COS(lat1)*SIN(lat2))-(SIN(lat1)*COS(lat1)*COS(lon2-lon1)), SIN(lon2-lon1)*COS(lat2)),2*PI())

First, remember that Excel has the atan2 parameters reversed so this is the Excel formula, but the atmega32 C code I’ll show in the next post will have the parameters the other way around.  I don’t know why the Excel atan2 function is different from the way everyone else implemented the function, but we have to live with it.  Second, notice the MOD() function with 2*pi().  The reason for this is we want our heading to be between 0-360 degrees or between 0 and 2 pi radians.  What this does is divide the result by 2 pi (360 degrees) and give us back the remainder.  If the result of the atan2 function is less than 2 pi it simply returns the number.  If it is greater, it divides and gives the remainder, which will always then be between 0 and 2 pi (0-360 degrees).   Here is a simple example using degrees.  Let’s say the atan2 function returns 380 degrees.  Dividing by 360 degrees gives a value of 1 with a remainder of 20.  The mod function discards the 1 and return 20.  380 degrees on a compass is the same as 20 degrees (20 degrees past 360).  So, the mod fuction wrapped around the atan2 function makes sure that the bearing is always between 0-360 degrees.

Now, all the trig functions in Excel use radians and atan2 is no exception so the formula above gives us the bearing in radians.  To convert to degrees we use the same formula shown above for lat/long:


So, let’s add this to our spreadsheet.  Now, point 2, our waypoint, has the same longitude as point 1 so these 2 points are directly north/south of each other.  Latitudes start at the equator and grow larger the farther north or south from the equator you go.  In this case, the latitude is near Denver, Colorado in the northern hemisphere, so larger values mean farther north and smaller values mean farther south.  Point 2, the waypoint, is 1 degree of latitude less than point 1, current position.  This means that the waypoint should be due south of current location at a distance of 69 miles.  Let’s look at the spreadsheet and see what we get.

Bearing in Excel

Bearing in Excel

The bearing in radians was pi, which when converted to degrees is 180 degrees.  Perfect.  We can try some other simple tests to make sure it works in other cases as well.  Due east would have latitude the same, but smaller longitude.  Let’s try that.

Due East

Due East

Rounding the result gives 90 degrees, what we expected.  You can try some other tests, for example put in the lat/lon of Los Angeles and New York City and see if the bearing makes sense.

You can download the Excel spreadsheet I used in the examples here:  DistanceAndBearingToWaypoint.xls

Deathpod3000 uses an Atmel AVR Atmega32 and is programmed with the free gcc C compiler.  The code is very similar, but there are some differences, like swapping parameter order for atan2.  I also created a relative bearing function to compute a -180 to 180 degree “steering angle” using current heading and bearing to waypoint.  This allows fairly easily proportional steering.  In the next post I’ll show and explain the code for doing all of this in C.



No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: