• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1772

# 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..
0
SullivanEnvironmental
1 Solution

Commented:
What do you mean by "average"? The average of 10 m/s west wind and 10 m/s east wind should be 0?
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/180)*speed" and E/W speed is "=SIN(degrees*3.1415926/180)*speed".

To obtain angle and speed back from the two averages, use
"=SQRT(nsavearge*nsaverage + ewaverage*ewaverage)"
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)
0

Commented:
Attached is an example. Please see if this works for you.
Temp.xls
0

Author Commented:
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
0

Commented:
If you wish to make it obvious that you are considering the vector properties of the wind, on each row calculate the N and E components of the measurement, average them, and recombine them at the end.
0

Author Commented:
DECENT
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.