SullivanEnvironmental
asked on
Excel Average Wind Speed and Wind Direction
I have this excel data
Timestamp Degrees meters/second
10/13/2010 7:30 182.0 1.0
10/13/2010 7:31 56.0 0.9
10/13/2010 7:32 40.0 0.8
10/13/2010 7:33 320.5 0.8
10/13/2010 7:34 55.0 1.0
10/13/2010 7:35 39.0 0.9
10/13/2010 7:36 36.0 0.7
10/13/2010 7:37 59.0 0.5
10/13/2010 7:38 43.0 0.5
10/13/2010 7:39 13.0 0.6
10/13/2010 7:40 20.0 0.8
I need to know the average wind direction and average speed at the bottom of each column. Note that this cant be a simple average because of the vector properties of wind. Thanks in advance..
Timestamp Degrees meters/second
10/13/2010 7:30 182.0 1.0
10/13/2010 7:31 56.0 0.9
10/13/2010 7:32 40.0 0.8
10/13/2010 7:33 320.5 0.8
10/13/2010 7:34 55.0 1.0
10/13/2010 7:35 39.0 0.9
10/13/2010 7:36 36.0 0.7
10/13/2010 7:37 59.0 0.5
10/13/2010 7:38 43.0 0.5
10/13/2010 7:39 13.0 0.6
10/13/2010 7:40 20.0 0.8
I need to know the average wind direction and average speed at the bottom of each column. Note that this cant be a simple average because of the vector properties of wind. Thanks in advance..
Attached is an example. Please see if this works for you.
Temp.xls
Temp.xls
ASKER
The code below applies to this and to something else - This code is being used to do this function in Fortran but I need to use this idea in excel. The code below shows how direction is being treated.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DECENT
In that case: convert to N/S and E/W speeds and average these.
In Excel speak, N/S speed is "=COS(degrees*3.1415926/18
To obtain angle and speed back from the two averages, use
"=SQRT(nsavearge*nsaverage
and
"=180/3.1415926 * ATAN2(nsaverage, ewaverage)"
(I don't have Excel available right now, so please check the angle formula against simple exaple data - maybe you need to swap the parameters)