lol.

No problem, glad to help. Array functions can seem a little funky at first
but once you get used to them they come in very handy.

--
Regards,
Dave
<!--


"Mike Punko" wrote:

OK after some modifying and great help from David, thanks man. I got it to
work. with a little modification I got my averages to come up on another
worksheet.

Sheet names (DATA, HOUR DATA)

DATA
TIME NUMBER 1 NUMBER 2
5:00:00 1 1
5:15:00 2 1.2
5:30:00 3 1.4
5:45:00 4 1.6
6:00:00 5 1.8
6:15:00 6 2
6:30:00 7 2.2
6:45:00 8 2.4
7:00:00 9 2.6
7:15:00 10 2.8
7:30:00 11 3
7:45:00 12 3.2
8:00:00 13 3.4

HOUR DATA
TIME Average 1 Average 2
5:00:00 2.5 1.3
6:00:00 6.5 2.1
7:00:00 10.5 2.9
8:00:00 14.5 3.7
9:00:00 18.5 4.5
10:00:00 22.5 5.3
11:00:00 26.5 6.1
12:00:00 30.5 6.9
13:00:00 33 7.4


Here's the formula for B2 on HOUR DATA
=AVERAGE(IF(HOUR(DATA!$A$2:$A$34)=HOUR(A2),DATA!$B $2:$B$34,""))

Thanks agian David, I remeber you helped me with the MTD average a few weeks
ago. I think I got this AVERAGE Formula imprinted on my forehead now, oh
wait that's my keyboard from all the head banging. lol



"kk" wrote:

Hi,

Try this...

=IF(MINUTE($A2)=0,AVERAGE(IF(HOUR($A$2:$A$14)=HOUR ($A2)-1,$B$2:$B$14,"")),"")

Confirmed with Ctrl + Shift + Enter



"Mike Punko" wrote in message
...

Thsi is what I'm getting

C2=AVERAGE(IF(HOUR(A2:A34)=6,B2:B34,""))
C3=AVERAGE(IF(HOUR(A3:A35)=6,B3:B35,""))

TIME NUMBER AVERAGE
5:00 1 #VALUE!
5:15 2 #VALUE!
5:30 3 #VALUE!
5:45 4 #VALUE!
6:00 5 19
6:15 6 19.5
6:30 7 20
6:45 8 20.5
7:00 9 #VALUE!
7:15 10 #VALUE!
7:30 11 #VALUE!
7:45 12 #VALUE!
8:00 13 #VALUE!

As you can see the average is a bit off, plus I need it to Average each
Hour.


"David Billigmeier" wrote:

Using a 24 hour clock (so a time of 4:24 PM would have an hour value of
16...) you can use this formula (array formula entered CTRL+SHIFT+ENTER)
which calculates the average of B2:B5 only if the hour value in column A
equals 16

=AVERAGE(IF(HOUR(A2:A5)=16,B2:B5,""))
--
Regards,
Dave
<!--


"Mike Punko" wrote:

ok first I apologize, I know this must have been covered already
somewhere.

Sheet1
A B C
1 TIME NUMBER AVERAGE
2 5:00 285.1 285.1
3 5:15 286.4
4 5:30 286.2
5 6:00 287.1 286.6
6 6:15 286.9

OK this is what I need, I have TIME (Actual data in per minute
5:00,5:01,5:02 etc) and NUMBER. What I need is to calculate the average
for
each hour. So at 5:00, 6:00, 7:00 etc. I need to calculate the average
for
4:00 to 4:59, 5:00 to 5:59, 6:00 to 6:59..etc.

I know this can be done with SUMPRODUCT but I just can't seem to get the
code to work for me. ANy help would be great, thanks!