This Excel template is initially made to convert geographic coordinates into UTM, from decimal format to degrees, minutes and seconds. Just what Contrary to the template we had previously done, As seen in the example:
- He concatenates them in a chain
- Converts them to coordinates UTM, With option to choose a Datum
- Concatenate the dot command to create points in AutoCAD with a single Copy / paste
- Concatenate the polyline command to draw the traverse with a Copy / paste
How the operation of converting geographic coordinates into UTM is done:
- To condition the input fields, you place properties on the cells. This is done with the Data tab, in the data validation option. We choose that there only support decimal data between -180 and 180 which is the maximum that supports the lengths. And then the error message indicates that the data is not allowed. In the case of latitudes, it is indicated between -90 and 90.
- To choose the hemisphere in lengths, which are in column G, the cell conditions, that if the coordinate is negative the W text is written, if positive text is placed E.
This is done with the formula = YES (G37 <0, »W», »E»)
- Similarly with the latitudes in column H, if the coordinate is negative, write the letter S, if it is positive N.
The formula will be = YES (H37 <0, »N», »S»)
- To extract the degrees, the absolute value is used and the number is truncated to zero decimals = ABS (TROUBLE (G37,0)) In this way, a -87.452140 will be converted to 87
- To extract the minutes, the original value of the truncated value is subtracted, so that only the decimals remain (0.452140) and that value is multiplied by 60, which is the total number of minutes in a degree. It is truncated to zero decimal places and thus it is obtained that in 0.452140 there are 27 minutes = TRUNC ((ABS (G37) -J37) * 60,0)
- To obtain the seconds, multiply the decimals (0.452140) by 3600 which is the number of seconds in a degree (60 × 60), and subtract what we have already subtracted, which are the minutes (27) multiplied by 60. Then rounding is applied, with a reference cell where the number of decimals is so that it can be adjusted to taste. So you have that there are 7.704 seconds. =REDONDEAR((((ABS(G37)-J37))*3600)-(K37*60),$L$5)
- To concatenate the point command, the _point string is applied, so that only the cells are copied to the AutoCAD command line = CONCATENATE («_ point«, ROUND (S37,2), »,», ROUND (T37,2)). Similarly, the polyline command = CONCATENATE («_ pline«, ROUND (S37,2), »,», ROUND (T37,2)). Rounding is applied so that chains are not too long.
In the template there are some tips to execute this last action.
From here you can download the template, paying with Credit card or Paypal.