Average wind direction? So if it blows from due east 1/2 the time and
due west the other half, then the average direction is nothing!

http://mathforum.org/library/drmath/view/57253.html

Rajin wrote:

I got problem of averaging 1 minute wind direction data for hours of a month.While using the formula by Max in your site I could do for other data. But wind direction such as 5 and 350 degree in the same hour gave me puzzling world. Could anyone help me



demechani wrote:

Averaging all values for each hour???
08-Jun-08

"Jennifer" wrote:

Ok, now that you have clarified it as above, here's my thoughts on your
issue ...

First, let's set the calc mode to manual mode since it's going to get quite
calc intensive. Click ToolsOptionsCalculation tabCheck "Manual"OK.

Assume data in cols A and B, from row1 down (as before)
In C1:
=DATE(YEAR(A$1),MONTH(A$1),DAY(A$1)+INT((ROWS($1:1 )-1)/24))
In D1:
=MOD(ROWS($1:1)-1,24)
Select C1:D1, copy down by 336 rows (14 days x 24 hourly band per day = 336
rows) to D336. Press F9 to recalc. Cols C and D will create all the labels to
denote the date/hour for each day based on your initial date data in A1. Each
date in col C will have all the 24 hourly bands, cycling 0-23 in col D.

Now place in E1, array-enter by pressing CTRL+SHIFT+ENTER:
=AVERAGE(IF((TEXT(A$1:A$3000,"dd-mmm-yy")=TEXT(C1,"dd-mmm-yy"))*(HOUR(A$1:A$3000)=D1)*(A$1:A$3000<""),B$1:B $3000))
Copy E1 down to E336. Again press F9 to recalc. When recalc is complete, you
should have the exact results that you seek in col E for each of the 14 days'
hourly bands.

Col E will return #DIV/0! where data in col A is missing/incomplete for the
particular date/hour. To suppress #DIV/0! errors, you could use an
IF(ISERROR(...) trap for the above (as before), viz, use this instead in E1,
array-entered then copied down:
=IF(ISERROR(AVERAGE(IF((TEXT(A$1:A$3000,"dd-mmm-yy")=TEXT(C1,"dd-mmm-yy"))*(HOUR(A$1:A$3000)=D1)*(A$1:A$3000<""),B$1:B $3000))),"",AVERAGE(IF((TEXT(A$1:A$3000,"dd-mmm-yy")=TEXT(C1,"dd-mmm-yy"))*(HOUR(A$1:A$3000)=D1)*(A$1:A$3000<""),B$1:B $3000)))