Using Macros To Perform Multiple Averages

[d__o__a]

In my Excel file i have over 1000 rows of data. This contains temperatures
measured each minute for each hour for each day over a month. Yes i know
that's a lot of data!

I want to be able to get an average hourly temperature for each hour of the
day as a way to simplify my spreadsheet. So basically what i want to do is
average the temperatures recorded between 9:00am and 9:59am for 9/3/2009, and
do the same thing for each hour of that day as well as every other day.

Is there a macro or something i can use to speed things up? I have tried
doing it the long way and it is of course pain-stakingly slow - in which case
i thought to myself there must be an easier and quicker way to do this!

Can anyone be able to help me?

Thanks,
--Max

 

[Joel]

You could use formulas

if your data is in column B and data/time is in column A add new column and
copy formula down column. INT will match the date. the 1st sumproduct gets
the totals and the 2nd sumproduct gets the number of sqamples for each hour.
the If statement is needed to only get an average for the last sample of each
hour.



=IF(HOUR(A1) <>
HOUR(A2),SUMPRODUCT(--(INT(A1)=INT(A$1:A$300)),--(HOUR(A1)=HOUR(A$1:A$300)),B$1:B$300)/SUMPRODUCT(--(INT(A1)=INT(A$1:A$300)),--(HOUR(A1)=HOUR(A$1:A$300))),
"")

 

[d__o__a]

I'm not well-equipped with Excel and all the formulas you may have to explain
everything in great detail. I tried what you said and i don't think it
worked. There are 59 rows for each hour and when i copy the formula into each
consecutive row it just gives an average for the next 59 lines, e.g. first
average goes into c2 = a2:a60, next average goes into c3 =a61:119, average in
c3 = a120:a179, etc, etc.

--Max

 

[d__o__a]

I'm not well-equipped with Excel and all the formulas you may have to explain
everything in great detail. I tried what you said and i don't think it
worked. There are 59 rows for each hour and when i copy the formula into each
consecutive row it just gives an average for the next 59 lines, e.g. first
average goes into c2 = a2:a60, next average goes into c3 =a61:119, average in
c3 = a120:a179, etc, etc.

--Max

 

[Joel]

I think the problem is you have a header row and the data is starting in row
2. try this change. the 300 should be the last row of the table.

=IF(HOUR(A2) <>
HOUR(A3),SUMPRODUCT(--(INT(A2)=INT(A$2:A$300)),--(HOUR(A2)=HOUR(A$2:A$300)),B$2:B$300)/SUMPRODUCT(--(INT(A2)=INT(A$2:A$300)),--(HOUR(A2)=HOUR(A$2:A$300))),
"")

 

[d__o__a]

Nope that still isn't doing what i want it to do

I'm not well-equipped with Excel and all the formulas you may have to explain
everything in great detail. I tried what you said and i don't think it
worked. There are 59 rows for each hour and when i copy the formula into each
consecutive row it just gives an average for the next 59 lines, e.g. first
average goes into c2 = a2:a60, next average goes into c3 =a61:119, average in
c3 = a120:a179, etc, etc.

--Max

 

[Joel]

I don't see how it is doing what you said. The IF statement says

HOUR(A2) <>HOUR(A3)


which means the on do the average when the hour in the current row doesn't
match the hour in the next row. If cell A2 has 00:00 AM (midnight) then A3
has 00:01 AM, A61 has 00:59 AM and A62 has 1:00 AM. the sum for row B will
be placed in Row 61 (not row 2 like you described).

 

[d__o__a]

Oh well i have worked out a way to do it which is reasonably quicker than
doing it manually.

In column C I type =if(a2<a61,average=b2:b61) - which means if a2 = 0:00am
is less than a61 = 0:59 then it finds the average for the temperatures
recorded between those times. However when i follow this formula down the
columns the references only move one row at a time. But i did it for all of
them then using a macro selecting every 60th row i was able to extrapolate
the averages for each hour

There is possibly an even quicker way of doing this but considering my
lack of excel formula and macro knowledge i'm pretty happy with what i
accomplished.

Thanks for your help - in which you made me think about what i wanted the
formula to say in relation to what i wanted to do. May not be exactly what
you said but i managed to get it done nevertheless.

Cheers,
--Max