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