Link to home
Start Free TrialLog in
Avatar of SullivanEnvironmental
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..
Avatar of Member_2_4694817
Member_2_4694817

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)
Attached is an example. Please see if this works for you.
Temp.xls
Avatar of SullivanEnvironmental

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of aburr
aburr
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial