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

Tagged as:

Author: Beni Raharjo; asal Jatitujuh - Majalengka Jawa Barat; SKMA Kadipaten (1997), fahutan UGM (1998); lives in Higashihiroshimashi. Blog www.raharjo.org
• http://klik-andri.co.cc andri

mmmh… so complicated..
not understand..sob

• Beni

Hi Andri.
Yo don’t need to understand the “how to”. If you are dealing with such problem for route planner using theodolite, just copy the excel file. Of course you need to adjust the number of rows etc using copy-paste the formulas down.

• Jerry Jones

I apologize…I did find your spreadsheet available to download upon second look. But what I still do not understand is the values in the X and Y columns. Is there a way to calculate those if all I have for each point is DMS?
Thanks again.

• Beni

Jerry,
I wrote X and Y in Universal Transverse Mercator Projection. It works with any projected coordinates. Of course, you need to adjust Distance unit with appropriate one. For example, if your projected coordinates are in yards, you need to change Distance unit with yards.

I am pretty sure, it does not work for non-projected coordinates. Its non equal length at longitude and latitude is the problem. You need to convert your coordinates from DMS (Geographic Coordinate System) into projected one.

• yayat supriyatna

Nuhun kang ilmunya neh, selama ini saya memakai rumus yang panjang,:)

• http://www.raharjo.org Beni

Sama-sama Yat, tapi kan rumusna juga panjang, cuma dipadatkan saja dalam satu kolom. Thanks atas komennya.

• yayat supriyatna

maksud saya bukan rumus nya yang panjang tapi car2 nya yang melalui beberapa tahap, bisa akang lihat di sini http://www.4shared.com/file/80203179/ea3dd371/KOORDINAT_AZIMUTH_JARAK1.html

• http://www.raharjo.org Beni

File itu juga tidak bisa dipakai karena tidak bisa memberikan derajat dalam kisaran 0-360. Coba deh dibeberapa kuadran, pasti ada derajat sampai 400an. Kalau file yang saya buat bermanfaat silakan dapat dipakai tanpa lisensi kok.

• ROBERTO OLABANJO

Your script solved my 2 months trouble of traverse computations in MS excel

• Pingback: Arcview 3x: Route Planner

• Nanang, Alas Purwo

kang…..gimana menyeting gsm tracking untuk nokia 6680 ??
matur nuwun……..

>>> Beni
Waduh maaf sy tidak tahu cara setting gsm tracking yang dimaksud

• hendra

Kang Beni, sekalian atuh kasi rumus merubah besaran Azimuth jadi sudut. untuk rekontruksi atau pemeliharaan batas kawasan biasanya pakai buku ukur yang masih menggunakan azimuth. tapi Karena sekarang kita pakai Theodolit yang hanya bisa mengukur jarak dan sudut, kita sulit menentukan Azimuth yang pas dengan theodolite tsb.

>>>Beni
File Excel yang saya attach tersebut perlu dimodifikasi. Nanti saya modif ya, tapi sekarang saya belum sempat.

• Roberto Scotti

Nice page, useful focalization on a commonly un-percieved problem.
Amore compact solution obviously exists,
it is based on the functions (Italian version of OpenOffice sintax)
ARCTAN.2(dy;dx) := http://en.wikipedia.org/wiki/Atan2
RESTO(y;x) := y-x*INT(y/x)

• http://www.raharjo.org/ Raharjo

Roberto, nice solution.

I have been complained by some people while having gis training: why the solution is very long and hard to be remembered

In Openoffice, the syntax works well. I will figure out how to use that syntax in MS Excel.

Thanks a lot

• ana

cara menghitung diferensial pake program excell gmn ya???

tolong donk bantu aku…

ato cara menghitung diferensial pake minitab ato matlab, ada yang tau ga???

• http://www.raharjo.org/ Raharjo

Ana, maaf saya tidak bisa bantu banyak. Kalau yang dimaksud adalah solving persamaan kompleks, bisa gunakan SPSS dan buat iterasinya.

• http://wildantoyib.info wildan

ana, bila anda terbiasa dengan matlab, untuk kalkulasi PD, silahkan dicoba http://www.mathworks.com/matlabcentral/newsreader/view_thread/263186
yang kita tau, matlab digunakan untuk simulasi dan elektro arus kuat,ntahlah utk ke gis

cara menghitung diferensial pake program excell gmn ya???
nah, kalo algoritma PD sudah dibuat, tuangkan saja ke dalam bahasa visual basic, run dg macro, baru deh, excel bisa kalkulasi persamaan differential.

semoga membantu.

• http://www.raharjo.org Beni

Nuhun Dan. Kamana wae tah teu nongol2.
Kaleresan abdi nuju ngadalami statistik spasial/geostatistik. Nyobian nganggo R sareng Matlab. Bagi2 resources nya Dan upami gaduh.

i use excel function named =ATAN2() for calculating azimuth.
assuming coordinates (X1,Y1) are stored in D19,E19
and (X2,Y2) are stored D21,E21accrodingly

azimuth is calculated as following
=DEGREES(ATAN2((E21-E19),(D21-D19)))+IF(ATAN2((E21-E19),(D21-D19))<0,360)

• ian

mas mau nanya gimana caranya menampilkan arah dan panjang dari sebuah garis ataupun poligon dalam sebuah table. mohon bantuannya…

• http://www.raharjo.org Beni

Kalau menggunakan MS Excel ya seperti dalam artikel ini Mas. Atau maunya di software GIS? Software GIS apa yang biasa dipakai?

• ian

Kang Ben saya menggunakkan arcgis 9.3

• joko

kang, mau nanya nih..aku apke kalkulator casio fx5800p,
klo mau masukin program untuk stake out..masukin formulanya gimana..
nuhun

• http://www.raharjo.org Beni

Wah saya sudah tidak punya casio yang dimaksud. Maaf ya.

• Sampurno Sigit

Kang Nanya Ya…bagai mana cara membuat program calkulator menggunakan excel macro..
contoh tampilannya seprti kalkulator kang..code programnya seperti apa ya kang …nuhun sangat

• http://www.raharjo.org Beni

Waduh, maaf mas. Saya tidak bisa bantu karena saya tidak mengerti.

• awirauhi

om guru mhn bantuannya ksh rumusan exel utk azimut jika kordinat awal
lat 02º55’27.98″

long 104º43’14.41″
lat 02º55’23.40″

long 104º43’22.80″
utk azimut degree =ATAN2((B22-B18);(B23-B19))*(180/PI()) = 61 degree (ini msh infant)
mhn bantuannya ndan untuk revisinya

• beni raharjo

Untuk satuan Derajat, menit, detik, formula sedikit berbeda karena memang konsepnya berbeda. Sebaiknya lakukan tahapan sbb

- Konversi semua DMS ke D (misal dari lat 02º55’27.98″ menjadi 28.2)