(English) MS Excel: Calculating Distance and Azimuth for Route Planner

Route planner is designed to be a guide for field operators in order to trace that route on the ground. The GPS availability does not help much due to higher accuracy requirements compare to the precision of GPS. Moreover, in some cases, we need to trace the route on the ground where GPS signal is strongly disturbed by buildings and/or forest canopy. Theodolite is probably the most suitable device for high precision route planner.

Route planner using terrestrial devices like theodolite need distance and azimuth  data connecting series of points in the route. There are many applications and or extensions to do this job. However, I do prefer MS Excel to do this job. First, I know what I need so I can just follow what I want to put. Not like using extensions in ArcView, for example, I need to meet their requirements. Second, if the errors taken place, I can trace the error in MS Excel and easily solve the problem.

The image (left) is an example of sketch of the route we plan. Field operators need to put a marker on every point (1 – 6). We can design the operators to start from point No 1, then measure point No 2, and so on until point No 6. Point No 1 as the base of measurement can be measured by many applications such as differential GPS measurement (if applicable). The question is how to provide distance and azimuth data of point 2 – 6?

We can export the coordinate of all points into MS Excel as illustrated below. The data contains ID, X, and Y. The ID is critical to determine the series of the points in the route. In this case, I export the coordinate data from ArcView 3x. The unit of X and Y are in meter. Then, I need to calculate the distance and azimuth from point 1 to point 2, from point 2 to point 3, etc.

Calculating the distance is pretty simple. We use Pythagoras theory. The distance between point 1 and point 2 (for example) is the square root of sum of square of each distance in X and Y. Confuse with that sentence? Just have a look the equation

It does not matter which one Y1 or Y2 ( X1 or X2). For the distance between point 1 and point 2 (for example), we can put Y1 as the Y of point 2 or the Y of point 1. The unit of distance (D) have to be agree with the unit of X and Y. In this case we use meter.

The next task is calculating the azimuth. In my country (Indonesia), we use degree (mostly in form degree, minute, second – DMS). Now, it is the real problem using MS Excel to do this. First, MS Excel uses radian as the angular unit instead of degrees. We can transform radians to degrees (or vice versa) by multiplying the radians unit with 180/pi (pi=3.14159…).That is quite easy, the first problem solved.

Second problem is mismatch of quadrant system between MS Excel and terrestrial system. If we put 90 degrees for terrestrial (azimuth) measurement, the direction will head to east (right). MS Excel recognizes 90 degree (in radian) heads to up (north). See the figure below.

(I have no idea naming of those systems. So, I just put that MS Excel uses left one and terrestrial measurement use right one)

What we need to do now is calculating azimuth in – let say – MS Excel system that produce the angular unit in terrestrial system. The combination of radian-degree conversions and “if” logic were performed. The example of the formula for azimuth can be shown in the following figure.

(The formula can be found in the provided Excel file at the bottom of this page. If you have simpler formula, please let me know. I will use the simple one and put in this page)

After azimuths (in degree) have been calculated, I usually convert this data into three values namely degree, minutes, and seconds. The following formulas were used

=ROUNDDOWN(F3,0)

=ROUNDDOWN((F3-G3)*60,0)

=(F3-(G3+(H3/60)))*3600

to calculate G3, H3, and I3 respectively (refer to image below)

Now, we have data for route design. Field operators can easily understand where they have to go by reading distance and azimuth.

Download the excel file here