"UDF" for volume calculation with the use of "NTF" method Lastne Excel funkcije za izračun prostornin po "NTF" metodi Milivoj Vulic 1, Anes Durgutovic 2 1University of Ljubljana, Faculty of Natural sciences and Engineering, Department of Geotechnology and Mining Engineering, Aškerčeva cesta 12, SI-1000 Ljubljana, Slovenia; E-mail: milivoj.vulic@ntf.uni-lj.si 2Oikos d.o.o., Jarška cesta 30, SI-1230 Ljubljana, Slovenia; E-mail: anes.durgutovic@oikos.si Received: September 25, 2007 Accepted: October 5, 2007 Abstract: In the article titled Calculation of volume with the use of "NTF" method the idea, of mathematical bases of the mentioned method, is presented. Therefore, we have by consideration of, in the mentioned article, shown results prepared »UDF« add-ins which enable realization of volume calculation with the use of MS Excel program. The preposition for the realization of volume calculation with already mentioned method is already carried out Delaney triangulation of the shape to which we wish to calculate the volume. In the article we wish to represent instructions for the use UDF which we use at volume calculation with the "NTF" method. Izvleček: V prispevku z naslovom Izračun volumnov z uporabo "NTF" metode je predstavljena zamisel in matematične osnove omenjene metode. Tako smo z upoštevanjem, v omenjenem prispevku, prikazanih ugotovitev pripravili dodatke »UDF«, ki nam omogočijo izvedbo izračuna volumnov z uporabo programa MS Excel. Seveda je predpogoj za izvedbo izračuna po omenjeni metodi izvedena Delaunay-eva triangulacija telesa kateremu prostornino želimo določiti. V prispevku vam želimo prikazati navodila za uporabo UDF-jev, ki jih uporabimo pri izračunu prostornine po metodi "NTF". Key words: volume calculation, MS Excel, UDF, outline, surface Ključne besede: izračun volumnov, MS Excel , UDF, kontura, ploskva Introduction Today, we can come across many different programs which on the basis of input data give results. However, the results we get with the use of programs (programs, which enable the calculation of the volume) vary among themselves. In order to find out why these particular programs give various results we would be bound to the background of their function. Because this would be difficult we have decided to prepare the method of volume calculation in the range of our own capacities. In this way we have made "NTF" method of volume calculation. The basis of this method and the mathematical background of the volume calculation are shown in the article calculation of the volume with the use of "NTF" method. The basis for the realization of volume calculation with already mentioned method is as in most of the programs already carried out Delaney triangulation of the shape to which we wish to calculate the volume. In order to carry out the volume calculation with the use of "NTF" method in the most simple way we have, on the basis of finding which is shown in the above mentioned article, prepared our own written functions (UDF) which enable realization of volume calculation with the use of MS Excel program. What is UDF? When we see the sign "UDF" we immediately ask ourselves what does it mean? However, we express it in a simple way, that this is "User Defined Function", which functions as add-ins in MS Excel program tools platform. Beside the functions offered by the program itself, we can therefore with the use of Microsoft Visual Basic for Applications prepare additional functions which enable faster work. And also possible pooling of resources of some repeated procedures. With "User Defined Function" we can solve more complicated mathematical tasks. We can use UDF when working with program on our own as well as other computer. UDF for volume calculation with the use of "NTF" method For the realization of Delaney triangulation of the upper and down surface we use software program »Triangel Net«, which is available for free on the internet. When we want to calculate the volume with the use of "NTF" method in MS Excel program we use the next UDF: • coEntW, • coVolW3ID, • linesSortedW3, • outLineW, • outPointsW. In the following, instructions for the use of UDF which we need for the realization of volume calculation with the use of "NTF" method in MS Excel program are presented. "coEntW" This function returns the value of elements from two different multitudes. And with it gives the sorted values on the basis of marked element and marked coordinates. Individual values can be a letter or number. Syntax: coEntW (CoorNames, CoordVal-ues, EntityPointKinds, EntityPoinlDs) Legend: • CoorNames names of coordinates (x, y, z), which value we wish to find. • CoordValues values of individual coordinate points, which we have gathered with field measurements. • EntityPointKinds are marks for the individual points of triangulated net. Marks can be numbers, letters or symbols. • EntityPoinlDs are identification values INPUT DATA RESULT CoordNames EntityPointKinds A A A A x y z A x y z 115 80 10 4 a = 4 120 79 13 116 79 11 e 1 1 116 79 11 115 81 10 JS 3 o PM 3 118 82 12 118 82 12 -3 r 0 y 0 115 80 10 120 79 13 O O 2 n w 2 115 81 10 119 82 15 U 121 83 11 of every triangle edges, which form a triangulated net. Use: 1. We select two roves more than we have coordinate points. 2. When selecting a column we must add up all the marks of individual set of point and multiply with the number of coordinates (we multiply with 2 if we have x,y and with 3 if we have x,y,z) 3. We use the function coEntW. 4. We choose the values according to the above written syntax. 5. When finished with the selection, we confirm the realization with a simultaneous press on the key Ctrl-Shift-Enter. 6. The function offers us the wanted result. "coVolW3ID" This function returns the value of elements from two different multitudes. And with it gives the sorted values on the basis of marked element and marked coordinates. Individual values can be a letter or number. Syntax: coVolW3ID (CoordNames, Coord-Values, EntityPointKinds, EntityPoinlDs, Zf Legend: • CoorNames names of coordinates (x, y, z), which value we wish to find. • CoordValues values of individual coordinate points, which we have gathered with field measurements. • EntityPointKinds are marks for the individual points of triangulated net. Marks can be numbers, letters or symbols. • EntityPoinlDs are identification values of every triangle edges, which form a triangulated net. • ZRef is a reference high. Use: 1. We select two roves more than we have triangle edges. 2. When selecting a column we must add up all the marks of individual set of point and multiply with the number of coordinates (we multiply with 2 if we have x, y and with 3 if we have x, y, z) and we add another column to this result for the value of partial volume. 3. We use the function coVolW3ID. 4. We choose the values according to the above written syntax. 5. When finished with the selection, we confirm the realization with a simultaneous press on the key Ctrl-Shift-Enter. INPUT DATA CoordNames EntityPointKinds x y z A B C 115 80 10 1 2 4 e = 116 79 11 e 2 3 1 115 81 10 0 1 3 o PM 118 82 12 > -3 3 4 0 y ti 120 79 13 r O o 4 0 2 n w 119 82 15 U 121 83 11 Z f 10 RESULT A A A B B B C C C 24,67 x y z x y z x y z 116 79 11 115 81 10 120 79 13 5,33 115 81 10 118 82 12 116 79 11 3,50 115 80 10 116 79 11 118 82 12 2,50 118 82 12 120 79 13 115 80 10 10,83 120 79 13 115 80 10 115 81 10 2,50 6. The function offers us the wanted result. "linesSortedW3" The functions offers us, from the individual edges of triangle net, classification of lines between two edges and also mark the ones which occur only once. It marks them with 0 or 1. Syntax: linesSortedW3 (Tri) - Tri is the mark of individual edges of triangle net, which we gained by Delaunay triangulation. Use: 1. We multiply the number of roves individual edges of triangle net with num- ber three. We choose so many roves, which will be equal to the gained result. 2. We choose three columns. 3. We use the function linesSortedW3. 4. We choose the values according to the above written syntax. 5. When finished with the selection, we confirm the realization with a simultaneous press on the key Ctrl-Shift-Enter. 6. The function offers us the wanted result. 7. In the cell above the third column we add up values of the third column. The value we get tells us how many contour lines we have. "outLineW" The function, from contour lines between two edges in triangle net, gives us only contour lines. Syntax: outLineW (OutLineWb, NumO-fOutLines) Legend: • OutLineWb are the lines between edges of triangle net, which we have gained Delaunay triangulation. • NumOfOutLines is the number of contour lines. That is the sum of value the third column at the function linesSort-edW3. Use: 1. We choose so many roves, as we have contour lines. 2. We choose two columns. 3. We use the function outLineW. 4. We choose the values according to the above written syntax. 5. When finished with the selection, we confirm the realization with a simultaneous press on the key Ctrl-Shift-Enter. 6. The function offers us the wanted result. "outPointsW" The function, from sorted contour lines between two edges in triangle net, gives us only contour point. Syntax: outPointsW (OutLines) OutLines is the sorted contour lines between two edges in triangle net. Use: 1. We choose so many roves, as we have contour lines and we add another roves for the mark of the multitudes. 2. We choose one columns. 3. We use the function outPoints. 4. We choose the values according to the above written syntax. 5. When finished with the selection, we INPUT DATA RESULT Tri From To Mark 1 2 4 0 1 1 2 3 1 0 2 1 0 1 3 0 3 0 3 4 0 0 3 0 4 0 2 0 4 0 0 4 0 1 2 0 1 2 0 1 3 0 1 3 0 1 4 1 2 3 1 2 4 0 2 4 0 3 4 1 INPUT DATA RESULT OutLineWb NumOfOutLines outLineW 0 1 1 5 0 1 0 2 1 0 2 0 3 0 1 4 0 3 0 2 3 0 4 0 3 4 0 4 0 1 2 0 1 2 0 1 3 0 1 3 0 1 4 1 2 3 1 2 4 0 2 4 0 3 4 1 confirm the realization with a simultaneous press on the key Ctrl-Shift-Enter. 6. The function offers us the wanted result. INPUT DATA RESULT OutLinesW outPointsW 0 1 0 0 2 1 1 4 2 2 3 3 3 4 4 Conclusions Presented UDF, which we need for the realization of volume calculation with the use of "NTF" method in MS Excel program, make our work easier. It is important to point out that when there is no definition of down surface it is possible, with the help of UDF ("linesSortedW3", "outLineW", "outPointsW") to pick out Points with which we determine surface which we adopt as down surface. By doing this we must pay attention to the given selection of outpoints and checks the given selection by drawing them in some program for designing. In this way we will check whether we got the actual shape of the outline surface. Visually presented example of the use of UDF is available on the web site: http:/www.geo.ntf.uni-lj.si/ mvulic. The example of how we can carry out volume calculation on a practical example with the use of UDF for the volume calculation with the "NTF" method will be given in the next article. Povzetki Lastne Excel funkcije za izračun prostornin po "NTF" metodi Predstavljene Lastne Excelove funkcije (UDF), ki jih potrebujemo za izračun prostornin po "NTF" metodi močno olajšajo naše delo. Pomembno j e izpostaviti prednost pri izračunu prostornin teles brez omejitve spodnje ploskve. V takih slučajih je možno z uporabo UDF ("linesSortedW3", "out- LineW", "outPointsW") definirati točke, ki določajo spodnjo omejitveno ploskev. Pri tej operaciji moramo točke predhodno določiti in preveriti s CAD programom. Na ta način lahko enostavno preverimo obliko omejitvene ploskve. Vizualizacija primera z uporabo UDF je dostopna na internet-nem naslovu: http:/www.geo.ntf.uni-lj.si/ mvulic. Prezentacija izračuna prostornine na praktičnem primeru pa bo predstavljena v naslednjem članku. References [1] VuLic, M. (2005): User Defined Func- tion. Naravoslovnotehniška fakulteta, Ljubljana, 2005. [2] VuLic, M. (2005): Matematične os- nove določanja prostornin tris-tranihprizem (interna predavanja). Naravoslovnotehniška fakulteta, Ljubljana, 2005. [3] VuLic, M. (2005): Osnove Delau- nay-eve triangulacije in Voronoi diagramov (interna predavanja). Naravoslovnotehniška fakulteta, Ljubljana, 2005. [4] VuLic, M., Durgutovič, A. (2006): Calculation of volume with the use of "NTF" method. RMZ - Materials and Geoenvironment.; Vol. 53, No. 2, pp. 221-228.