[Jennifer]

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!!!
:)

[Max]

Assume data in cols A and B, from row1 down

In C1:C24, list the 24 numbers: 0,1,2,3,.. 23 to denote the 24 hourly bands
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 C

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$1 00)=C1)*(A$1:A$100<""),B$1:B$100)))

Adapt the ranges to suit the actual extents of your data
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jennifer" wrote:
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!!!
:)

[joel]

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)))

"Jennifer" wrote:

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!!!
:)

[Jennifer]

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??

:(



"Jennifer" wrote:

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!!!
:)

[joel]

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.


"Jennifer" wrote:

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??

:(



"Jennifer" wrote:

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!!!
:)

[Jennifer]

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!





"Joel" wrote:

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.


"Jennifer" wrote:

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??

:(



"Jennifer" wrote:

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!!!
:)

[Jennifer]

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



"Max" wrote:

Assume data in cols A and B, from row1 down

In C1:C24, list the 24 numbers: 0,1,2,3,.. 23 to denote the 24 hourly bands
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 C

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$1 00)=C1)*(A$1:A$100<""),B$1:B$100)))

Adapt the ranges to suit the actual extents of your data
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jennifer" wrote:
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!!!
:)

[joel]

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$1 00)=C1)*(A$1:A$100<""),B$1:B$100)))
"Jennifer" wrote:

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!





"Joel" wrote:

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.


"Jennifer" wrote:

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??

:(



"Jennifer" wrote:

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!!!
:)

[Max]

"Jennifer" wrote:
.. 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......
My data has over 2000 points ..

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)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Max]

I've responded further to you in the other branch
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Rajin Aryal]

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)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Previous Posts In This Thread:

On Saturday, June 07, 2008 6:52 PM
Jennife wrote:

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!!!

On Saturday, June 07, 2008 8:11 PM
demechani wrote:

Averaging all values for each hour???
Assume data in cols A and B, from row1 down

In C1:C24, list the 24 numbers: 0,1,2,3,.. 23 to denote the 24 hourly bands
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 C

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$1 00)=C1)*(A$1:A$100<""),B$1:B$100)))

Adapt the ranges to suit the actual extents of your data
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jennifer" wrote:

On Saturday, June 07, 2008 8:14 PM
Joe wrote:

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)))

"Jennifer" wrote:

On Saturday, June 07, 2008 8:52 PM
Jennife wrote:

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??




"Jennifer" wrote:

On Saturday, June 07, 2008 9:49 PM
Joe wrote:

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.


"Jennifer" wrote:

On Saturday, June 07, 2008 10:40 PM
Jennife wrote:

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!





"Joel" wrote:

On Saturday, June 07, 2008 10:52 PM
Jennife wrote:

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



"Max" wrote:

On Sunday, June 08, 2008 12:08 AM
Joe wrote:

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$1 00)=C1)*(A$1:A$100<""),B$1:B$100)))
"Jennifer" wrote:

On Sunday, June 08, 2008 5:51 AM
demechani wrote:

Averaging all values for each hour???
"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)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

On Sunday, June 08, 2008 11:17 AM
Max wrote:

I've responded further to you in the other branch--
I have responded further to you in the other branch
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk Custom Pipeline for Splitting Messages
http://www.eggheadcafe.com/tutorials...ipeline-f.aspx

[David Biddulph[_2_]]

If values were 359, 119, and 239, what would you want the average to be as
they are each 120 deg apart?
What is the average of 1, 121, and 241, as they are again 120 deg apart?
359, 89, 179, 269?
1, 91, 181, 271?
--
David Biddulph


"Rajin Aryal" wrote in message ...
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)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Previous Posts In This Thread:

On Saturday, June 07, 2008 6:52 PM
Jennife wrote:

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!!!

On Saturday, June 07, 2008 8:11 PM
demechani wrote:

Averaging all values for each hour???
Assume data in cols A and B, from row1 down

In C1:C24, list the 24 numbers: 0,1,2,3,.. 23 to denote the 24 hourly
bands
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 C

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$1 00)=C1)*(A$1:A$100<""),B$1:B$100)))

Adapt the ranges to suit the actual extents of your data
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jennifer" wrote:

On Saturday, June 07, 2008 8:14 PM
Joe wrote:

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)))

"Jennifer" wrote:

On Saturday, June 07, 2008 8:52 PM
Jennife wrote:

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??




"Jennifer" wrote:

On Saturday, June 07, 2008 9:49 PM
Joe wrote:

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.


"Jennifer" wrote:

On Saturday, June 07, 2008 10:40 PM
Jennife wrote:

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!





"Joel" wrote:

On Saturday, June 07, 2008 10:52 PM
Jennife wrote:

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



"Max" wrote:

On Sunday, June 08, 2008 12:08 AM
Joe wrote:

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$1 00)=C1)*(A$1:A$100<""),B$1:B$100)))
"Jennifer" wrote:

On Sunday, June 08, 2008 5:51 AM
demechani wrote:

Averaging all values for each hour???
"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)))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

On Sunday, June 08, 2008 11:17 AM
Max wrote:

I've responded further to you in the other branch--
I have responded further to you in the other branch
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


Submitted via EggHeadCafe - Software Developer Portal of Choice
BizTalk Custom Pipeline for Splitting Messages
http://www.eggheadcafe.com/tutorials...ipeline-f.aspx

[Bob I]

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)))