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:
Re: Averaging all values for each hour???
08-Jun-08
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 Tools>Options>Calculation tab>Check "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
1, 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))
--
Ma
Singapor
http://savefile.com/projects/23689
xdemechani
---
Previous Posts In This Thread:
Averaging all values for each hour???
I have been trying for hours to try to figure this out. I have data that has
been recorded every few minutes over a two week period for temperature, say
time is in column A in mm/dd/yy hh:mm:ss and my temperature is in column B. I
have many different data sets not taken at equal times. Is there a way to
Average the temperature over each hour? so... formulate an If statement or
something like that where If(still in the same hour):then(add to previous
total/average)
Any help would be great!!!
Re: Averaging all values for each hour???
Assume data in cols A and B, from row1 dow
In C1:C24, list the 24 numbers: 0,1,2,3,.. 23 to denote the 24 hourly band
Place in D1, array-enter it by pressing CTRL+SHIFT+ENTER,
instead of just pressing ENTER
=AVERAGE(IF((HOUR(A$1:A$100)=C1)*(A$1:A$100<>""),B$1:B$100)
Copy down to D24 to return the results for the 24 hourly bands in col
And to suppress #DIV/0! errors,
you could use an IF(ISERROR(...) trap for the above, viz
=IF(ISERROR(AVERAGE(IF((HOUR(A$1:A$100)=C1)*(A$1:A$100<>""),B$1:B$100))),"",AVERAGE(IF((HOUR(A$1:A$100)=C1)*(A$1:A$100<>""),B$1:B$100))
Adapt the ranges to suit the actual extents of your dat
--
Ma
Singapor
http://savefile.com/projects/23689
xdemechani
--
:
The formula below gets the average for the hour in cell A15.
The formula below gets the average for the hour in cell A15. Change the
Range of cell (a1:A15 and B1:B15) to include the entire range of data
the formula gets the correct data by using INT() to make sure you only
include the hour for the correct data. then it uses Hour to check for data
with the correct hour. the formula uses Sumproduct twice and then divides.
the Top half of the fraction is the totals temperature for the correct hour.
the bottom part of the fraction is the number of cells for the correct hour
=SUMPRODUCT(--(INT($A1:$A15)=INT(A15)),--(HOUR($A1:$A15)=HOUR(A15)),B1:B15)/SUMPRODUCT(--(INT($A1:$A15)=INT(A15)),--(HOUR($A1:$A15)=HOUR(A15))
:
The other thing to keep in mind is that my data set is over a two week period,
The other thing to keep in mind is that my data set is over a two week
period, not over one day...so the date is something else to watch! Why is
this so complicated??
:
RE: Averaging all values for each hour???
Jennifer: To perform this average you need to calculate the sum and divide by
the total number of enties that meet the requirements. A date is a number
with 1 starting at Jan 1, 1900 and incrementing by one for each Day. Hours
are a fraction of a day which is 1/24 for each hour and 1/(24 * 60) for each
minute.
Suppose your time is 6/7/08 6:30 AM the number for this is 39606.2708333333
First you have to extract the correct date which is 39606. To get this
number you use the INT function. The hour is .25 which is (6 hours/24
hours). The Hour function will get this information. Now you have to
compare both the Day and Hour to get the correct data.
You need to perform this test in both numerator (the total) and the
denominator (the number of values to sum). This account for 3 different
terms in the numerator and and 2 diferent terms in the denominator. The
function needs 5 different terms which is why the formula looks complicated.
:
Thanks for all your effort and help.
Thanks for all your effort and help. What I meant was, I need a different
data point for each hour of each day, not one average for all 6am times, but
one 6am average for 2/1 and for 2/2, 2/3, etc......
I don't know if I'm just retarded or what but I can't seem to get your
program to work either
My data has over 2000 points. I was able to get
Max's to work until the last few hours became Divide By Zero and I haven't
been able to fix that one yet either. SO CONFUSING!
:
Your program works well for finding the average for each hour.
Your program works well for finding the average for each hour. I feel like
I'm close but not quite. My date and time is actually in the C column and
I'm actually looking at the wind speed which is in the F column. I modified
your program to be this:
=AVERAGE(IF((HOUR(C$2:C$2088)=J2)*(C$2:C$2088<>""),F$2:F$2088))
What I am interested in looking for in addition to an overall average, is a
way to find the DAILY average of the winds for each hour, so that I have
average winds ffor 2/1, 2/2, 2/3, etc. This works GREAT and I am extremely
grateful you helped me with this, is there any way to modify it for each day
as well??
example stuff so you can get some idea:
Date Time Date and Time Air Temperature Barom P (mb) Wind Speed (m/s)
1-Feb-02 0:06:48 2/01/2002 00:06:48 12.04 1025 3.63
1-Feb-02 0:16:48 2/01/2002 00:16:48 12.12 1025 2.9
1-Feb-02 0:26:48 2/01/2002 00:26:48 12.09 1025 2.17
1-Feb-02 0:36:48 2/01/2002 00:36:48 12 1025 1.68
....
12-Feb-02 16:52:52 2/12/2002 16:52:52 15.71 1014 4.16
12-Feb-02 17:02:52 2/12/2002 17:02:52 15.73 1014 4.25
Thank you!
jennifer
:
I took the day check out of my code and also removed the divide by 0 problem.
I took the day check out of my code and also removed the divide by 0 problem.
=if(SUMPRODUCT(--(HOUR($A1:$A15)=HOUR(A15))=0,0,SUMPRODUCT(--(HOUR($A1:$A15)=HOUR(A15)),B1:B15)/SUMPRODUCT(--(HOUR($A1:$A15)=HOUR(A15))))
This is the fix I did for Max's formula to remove the divide by zero
=IF(SUM(--(HOUR(A$1:A$100)=C1))=0,0,AVERAGE(IF((HOUR(A$1:A$100)=C1)*(A$1:A$100<>""),B$1:B$100)))
:
Re: Averaging all values for each hour???
:
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 Tools>Options>Calculation tab>Check "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
1, 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)))
---
I've responded further to you in the other branch--
I have responded further to you in the other branch
---
Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk Custom Pipeline for Splitting Messages
http://www.eggheadcafe.com/tutorial...6-241d5e325f42/biztalk-custom-pipeline-f.aspx
