How is this applicable to Exel?

 

Consider two sets of coordinates x1,y1 & x2,y2. If we first process the first part of the equation ((X1–X2)²+(Y1-Y2)²) below, we get -121103. Excel, of course, won’t square root a negative number so we have to put in a conversion. IF the number is negative (see below, using IF and ABS), we can then square root the positive number, which provides the answer 347.9986. (This is a metric coordinate system using metres.) Therefore the distance is 348m

 

x1	y1	x2	y2	Apply formula but don’t root	Change if negative	Answer
612109.00	5639930.00	612108.00	5640278.00	-121103	121103	347.9986
((A2-C2)^2-(B2-D2)^2)	IF(E2<0,ABS(E2),E2)

Although it might be easier to use the measure tool if you have the GIS open, if you are faced with a ream of coordinates, this method might be a lifesaver.

2. Change from Degrees, Minutes, Seconds to Decimal Degrees

This one is close to my heart, though  I often see it being some mammoth task undertaken by someone as they use some coordinate converter or conversion tool. This can be done quickly and easily in your favourite spreadsheet.

A	B	C	D	E	F
Latitude	Longitude	Latitude                 (for conversion)	Longitude                     (for conversion)	Latitude	Longitude
				(decimal degree)	(decimal degree)
42° 43′ 5″ N	71° 12′ 37″ W	42D 43′ 5″ N	71D 12′ 37″ W	42.71805556	-71.21027778
42° 36′ 7.880″ N	71° 10′ 17.400″ W	42D 36′ 7.880″ N	71D 10′ 17.400″ W	42.60218889	-71.1715
42° 26′ 31.65″ N	71° 6′ 16.16″ W	42D 26′ 31.65″ N	71D 6′ 16.16″ W	42.442125	-71.10448889
42° 26′ 31.65″ N	71° 6′ 16.16″ W	42D 26′ 31.65″ N	71D 6′ 16.16″ W	42.442125	-71.10448889

There are [again] a couple of steps required to do this, but if you have a spreadsheet already set up, you can just add your values and extract your numbers.

First task using the method here is to convert the coordinates in columns A&B so that instead of having the degrees symbol, it uses the letter “D.” this should be put into columns C&D.

Then I use this equation to convert C4 to the result in E4:  =SUBSTITUTE(SUBSTITUTE(LEFT(C4,LEN(C4)-3),”D “,”:”),”‘ “,”:”)*IF(RIGHT(D4,1)=”S”,-24,24)

To convert D4 into the result in F4 I use this equation:  =SUBSTITUTE(SUBSTITUTE(LEFT(C4,LEN(C4)-3),”D “,”:”),”‘ “,”:”)*IF(RIGHT(D4,1)=”S”,-24,24)

As you can see above, this works fine, and as long as you put it in correctly you can just use “Fill” to populate the other coordinates easily. Best thing is I know EXACTLY what this is doing to my coordinates!

To add another freebing here – if you are working with offshore data, you will frequently find data supplied in Degrees, Decimal Minutes (DDM) to convert this to Decimal Degrees use the following method:

A	B	C	D
Latitude DDM	Longitude DDM	Latitude	Longitude
		(decimal degree)	(decimal degree)
42° 43.5′ N	71° 12.37′ W	42.725	-71.205
47° 41.5′ N	71° 12.37′ W	47.692	-71.205
21° 25.5′ N	71° 12.37′ W	21.425	-71.205

 To convert A4 (42° 43.5′ N) to decimal degrees, put this in C4: =IF(RIGHT(A4,1)=”S”,ROUND(LEFT(A4,2)+MID(A4,4,6)/60, 3)*-1, ROUND(LEFT(A4,2)+MID(A4,4,6)/60, 3))

 To convert B4 (71° 12.37′ W) to decimal degrees, put this in D4: =IF(RIGHT(B4,1)=”W”,ROUND(LEFT(B4,2)+MID(B4,4,6)/60, 3)*-1, ROUND(LEFT(B4,2)+MID(B4,4,6)/60, 3))

You will find that this will also convert west and east coordinates into negative values.

3. Calculate LiDAR RMSE (Root Mean Square Error)

1. In cell A2, type “observed” as a title. In B2, type “predicted value”. In C2, type “difference”.

2. If you have 10 observations, place observed elevation values in A3 to A12. Place predicted values in B3 to B12.

3. In column C3, subtract observed value and predicted value: =A3-B3. Repeat for all rows below where predicted and observed values exist.

4. In cell D3, use the following formula to calculate RMSE: =SQRT(SUMSQ(C3:C12)/COUNTA(C3:C12))

Cell D3 is the root mean square error value

Nick D