Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

I have a four sided field with a center point (that is not really in the center) but is called center.

The four corners and "center" point are all known gps coordinates (known in UTM, Long Lat). I need an excel calculation that will allow me to automatically calculate the distance from the "center" point to the edge of the field from all 365 degrees. Assume that the four "corners" of the field can be connected to form a square if this makes this easier. I also need this formula to be generic enough so that it can accommodate any 4 corners and "center point" and give me the distance automatically.

For instance, if I have a column 1a that says 231 (degrees), id like column 1b to say the distance to the edge of the field given 231 degrees from the "center" point

Thanks in advance

The four corners and "center" point are all known gps coordinates (known in UTM, Long Lat). I need an excel calculation that will allow me to automatically calculate the distance from the "center" point to the edge of the field from all 365 degrees. Assume that the four "corners" of the field can be connected to form a square if this makes this easier. I also need this formula to be generic enough so that it can accommodate any 4 corners and "center point" and give me the distance automatically.

For instance, if I have a column 1a that says 231 (degrees), id like column 1b to say the distance to the edge of the field given 231 degrees from the "center" point

Thanks in advance

SW Corner 46 4 36.412 119 27 32.459

NW Corner 46 4 39.182 119 27 31.94

NE Corner 46 4 38.163 119 27 27.241

SE Corner 46 4 35.613 119 27 28.933

```
C Last change: MTH 26 Oct 2010 4:27 pm
CHARACTER*20 DUM
INTEGER A
INTEGER PERIOD(33), Wind1, Wind2, Wind3, Wind4, Wind5
REAL SUMWS1, SUMWD1, SUMWS2, SUMWD2, SUMWS3, SUMWD3, SUMWS4
REAL SUMWD4, SUMWS5, SUMWD5, Angle, Fetch1, Fetch2, Fetch3
REAL Fetch4, Fetch5, SumFetch1, SumFetch2, SumFetch3, SumFetch4
REAL SumFetch5, X, Y
REAL WS1, WS2, WS3, WS4, WS5, WD1, WD2, WD3, WD4, WD5
REAL D1, D2, D3, D4, D5, D11, D12, D13, D14, D15
OPEN(1,FILE='ws1.prn',STATUS='OLD')
OPEN(2,FILE='wd1.prn',STATUS='OLD')
OPEN(4,FILE='met1.out',STATUS='UNKNOWN')
DATA PERIOD / 4, 6, 6, 6, 6, 6, 6, 6, 12, 12, 12, 12, 12, 12, 12,
x 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12,
x 12, 12, 12, 14, 12/
Angle=45
X=68
Y=68
Pi=3.14159
MaxFetch=SQRT(X*X+Y*Y)/2
*** Set cumulative variables to zero for start of minute-hourly dataset conversion routine ***
DO A=1, 33
Wind1=0
SUMWD1=0
SUMWS1=0
Wind2=0
SUMWD2=0
SUMWS2=0
Wind3=0
SUMWD3=0
SUMWS3=0
Wind4=0
SUMWD4=0
SUMWS4=0
Wind5=0
SUMWD5=0
SUMWS5=0
SumFetch1=0
SumFetch2=0
SumFetch3=0
SumFetch4=0
SumFetch5=0
DO B=1,PERIOD(A)*60
READ(1,'(6F8.2)',END=20) WS1, WS2, WS3, WS4, WS5
READ(2,'(6F8.2)') WD1, WD2, WD3, WD4, WD5
IF (WS1.NE.0) THEN
Wind1=Wind1+1
IF (Wind1.EQ.1) THEN
D11=WD1
SUMWD1=D11
ELSE
TEST=WD1-D11
IF (TEST.LT.-180) THEN
D1=D11+TEST+360
ELSE IF (TEST.LT.180.AND.TEST.GT.-180) THEN
D1=D11+TEST
ELSE IF (TEST.GT.180) THEN
D1=D11+TEST-360
END IF
SUMWD1=SUMWD1+D1
D11=D1
END IF
END IF
SUMWS1=SUMWS1+WS1
IF (WD1-45.LT.0) THEN
Fetch1=WD1-45+360
ELSE
Fetch1=WD1-45
END IF
IF (Fetch1.GT.90) THEN
IF (Fetch1.GE.180) THEN
IF (Fetch1.GE.270) THEN
Fetch1=360-Fetch1
ELSE
Fetch1=Fetch1-180
END IF
ELSE
Fetch1=180-Fetch1
END IF
ELSE
Fetch1=Fetch1
END IF
COSA=ABS(0.5*Y/(COS(Fetch1*Pi/180)))
SINA=ABS(0.5*X/(SIN(Fetch1*Pi/180)))
IF (COSA.GT.MaxFetch) THEN
Fetch1=SINA
ELSE
Fetch1=COSA
END IF
IF (WS2.NE.0) THEN
Wind2=Wind2+1
IF (Wind2.EQ.1) THEN
D12=WD2
SUMWD2=D12
ELSE
TEST=WD2-D12
IF (TEST.LT.-180) THEN
D2=D12+TEST+360
ELSE IF (TEST.LT.180.AND.TEST.GT.-180) THEN
D2=D12+TEST
ELSE IF (TEST.GT.180) THEN
D2=D12+TEST-360
END IF
SUMWD2=SUMWD2+D2
D12=D2
END IF
END IF
SUMWS2=SUMWS2+WS2
IF (WD2-45.LT.0) THEN
Fetch2=WD2-45+360
ELSE
Fetch2=WD2-45
END IF
IF (Fetch2.GT.90) THEN
IF (Fetch2.GE.180) THEN
IF (Fetch2.GE.270) THEN
Fetch2=360-Fetch2
ELSE
Fetch2=Fetch2-180
END IF
ELSE
Fetch2=180-Fetch2
END IF
ELSE
Fetch2=Fetch2
END IF
COSA=ABS(0.5*Y/(COS(Fetch2*Pi/180)))
SINA=ABS(0.5*X/(SIN(Fetch2*Pi/180)))
IF (COSA.GT.MaxFetch) THEN
Fetch2=SINA
ELSE
Fetch2=COSA
END IF
IF (WS3.NE.0) THEN
Wind3=Wind3+1
IF (Wind3.EQ.1) THEN
D13=WD3
SUMWD3=D13
ELSE
TEST=WD3-D13
IF (TEST.LT.-180) THEN
D3=D13+TEST+360
ELSE IF (TEST.LT.180.AND.TEST.GT.-180) THEN
D3=D13+TEST
ELSE IF (TEST.GT.180) THEN
D3=D13+TEST-360
END IF
SUMWD3=SUMWD3+D3
D13=D3
END IF
END IF
SUMWS3=SUMWS3+WS3
IF (WD3-45.LT.0) THEN
Fetch3=WD3-45+360
ELSE
Fetch3=WD3-45
END IF
IF (Fetch3.GT.90) THEN
IF (Fetch3.GE.180) THEN
IF (Fetch3.GE.270) THEN
Fetch3=360-Fetch3
ELSE
Fetch3=Fetch3-180
END IF
ELSE
Fetch3=180-Fetch3
END IF
ELSE
Fetch3=Fetch3
END IF
COSA=ABS(0.5*Y/(COS(Fetch3*Pi/180)))
SINA=ABS(0.5*X/(SIN(Fetch3*Pi/180)))
IF (COSA.GT.MaxFetch) THEN
Fetch3=SINA
ELSE
Fetch3=COSA
END IF
IF (WS4.NE.0) THEN
Wind4=Wind4+1
IF (Wind4.EQ.1) THEN
D14=WD4
SUMWD4=D14
ELSE
TEST=WD4-D14
IF (TEST.LT.-180) THEN
D4=D14+TEST+360
ELSE IF (TEST.LT.180.AND.TEST.GT.-180) THEN
D4=D14+TEST
ELSE IF (TEST.GT.180) THEN
D4=D14+TEST-360
END IF
SUMWD4=SUMWD4+D4
D14=D4
END IF
END IF
SUMWS4=SUMWS4+WS4
IF (WD4-45.LT.0) THEN
Fetch4=WD4-45+360
ELSE
Fetch4=WD4-45
END IF
IF (Fetch4.GT.90) THEN
IF (Fetch4.GE.180) THEN
IF (Fetch4.GE.270) THEN
Fetch4=360-Fetch4
ELSE
Fetch4=Fetch4-180
END IF
ELSE
Fetch4=180-Fetch4
END IF
ELSE
Fetch4=Fetch4
END IF
COSA=ABS(0.5*Y/(COS(Fetch4*Pi/180)))
SINA=ABS(0.5*X/(SIN(Fetch4*Pi/180)))
IF (COSA.GT.MaxFetch) THEN
Fetch4=SINA
ELSE
Fetch4=COSA
END IF
IF (WS5.NE.0) THEN
Wind5=Wind5+1
IF (Wind5.EQ.1) THEN
D15=WD5
SUMWD5=D15
ELSE
TEST=WD5-D15
IF (TEST.LT.-180) THEN
D5=D15+TEST+360
ELSE IF (TEST.LT.180.AND.TEST.GT.-180) THEN
D5=D15+TEST
ELSE IF (TEST.GT.180) THEN
D5=D15+TEST-360
END IF
SUMWD5=SUMWD5+D5
D15=D5
END IF
END IF
SUMWS5=SUMWS5+WS5
IF (WD5-45.LT.0) THEN
Fetch5=WD5-45+360
ELSE
Fetch5=WD5-45
END IF
IF (Fetch5.GT.90) THEN
IF (Fetch5.GE.180) THEN
IF (Fetch5.GE.270) THEN
Fetch5=360-Fetch5
ELSE
Fetch5=Fetch5-180
END IF
ELSE
Fetch5=180-Fetch5
END IF
ELSE
Fetch5=Fetch5
END IF
COSA=ABS(0.5*Y/(COS(Fetch5*Pi/180)))
SINA=ABS(0.5*X/(SIN(Fetch5*Pi/180)))
IF (COSA.GT.MaxFetch) THEN
Fetch5=SINA
ELSE
Fetch5=COSA
END IF
SUMFETCH1=SUMFETCH1+Fetch1
SUMFETCH2=SUMFETCH2+Fetch2
SUMFETCH3=SUMFETCH3+Fetch3
SUMFETCH4=SUMFETCH4+Fetch4
SUMFETCH5=SUMFETCH5+Fetch5
END DO
SUMFETCH1=SUMFETCH1/(PERIOD(A)*60)
SUMFETCH2=SUMFETCH2/(PERIOD(A)*60)
SUMFETCH3=SUMFETCH3/(PERIOD(A)*60)
SUMFETCH4=SUMFETCH4/(PERIOD(A)*60)
SUMFETCH5=SUMFETCH5/(PERIOD(A)*60)
SUMWS1=SUMWS1/(60*Period(A))
SUMWD1=SUMWD1/Wind1
SUMWS2=SUMWS2/(60*Period(A))
SUMWD2=SUMWD2/Wind2
SUMWS3=SUMWS3/(60*Period(A))
SUMWD3=SUMWD3/Wind3
SUMWS4=SUMWS4/(60*Period(A))
SUMWD4=SUMWD4/Wind4
SUMWS5=SUMWS5/(60*Period(A))
SUMWD5=SUMWD5/Wind5
IF (SUMWD1.LT.0) THEN
SUMWD1=360+SUMWD1
ELSE IF (SUMWD1.GT.360) THEN
SUMWD1=SUMWD1-360
END IF
IF (SUMWD2.LT.0) THEN
SUMWD2=360+SUMWD2
ELSE IF (SUMWD2.GT.360) THEN
SUMWD2=SUMWD2-360
END IF
IF (SUMWD3.LT.0) THEN
SUMWD3=360+SUMWD3
ELSE IF (SUMWD3.GT.360) THEN
SUMWD3=SUMWD3-360
END IF
IF (SUMWD4.LT.0) THEN
SUMWD4=360+SUMWD4
ELSE IF (SUMWD4.GT.360) THEN
SUMWD4=SUMWD4-360
END IF
IF (SUMWD5.LT.0) THEN
SUMWD5=360+SUMWD5
ELSE IF (SUMWD5.GT.360) THEN
SUMWD5=SUMWD5-360
END IF
WRITE(4,'(I5,15F8.2)') A,
x SUMWS1, SUMWS2, SUMWS3, SUMWS4, SUMWS5,
x SUMWD1, SUMWD2, SUMWD3, SUMWD4, SUMWD5,
x SumFetch1, SumFetch2, SumFetch3, SumFetch4,
x SumFetch5
20 END DO
STOP
END
```

Here is another possible solution: Turn latitude/longitude into X,Y coordinates

The formulas have been inserted in column P for reference and have been acquired from

http://www.movable-type.co.uk/scripts/latlong.html

Saqib

DistLatLong.xls

Title: Calculate distance, bearing and more between Latitude/Longitude points

Link: http://www.movable-type.co.uk/scripts/latlong.html

Title: Intersection point of two lines (2 dimensions)

Link: http://paulbourke.net/geometry/lineline2d/

Kevin

Q-26974315.xls

Center W119 30 26.80 N46 06 40.64 -119.50744 46.11129

Top Right Corner W119 29 46.82 N46 06 58.75 -119.49634 46.11632

Top Left Corner W119 31 00.03 N46 07 00.55 -119.51668 46.11682

Bottom Left Corner W119 30 59.98 N46 06 07.78 -119.51666 46.10216

Bottom Right Corner W119 29 46.13 N46 06 05.90 -119.49615 46.10164

12.jpg

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Kevin

Q-26974315.xls