Average based on Hour

[Guest]

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!

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

Couple of things:

1) You are probably getting the #VALUE! because you aren't entering the
function by pressing "Ctrl+Shift+Enter"... If you JUST press "Enter" you will
get that error because this is an array formula.

2) In your formula you have the hour check equal to 6. This is OK if all
of your times are equal to 6 AM (that's 6 in the morning). If they are
formatted 6 PM you need to set the check equal to 18 (because hour 18 = 6PM
on a 24 hour clock)... you have to check which one. An easy check is to put
the formula =HOUR(A2) in an empty cell to see if it yields 5 or 17.

3) Why are you dragging the function in C2 down (that is why the function
in C3 is one row incremented, so you go from A2:A34 to A3:A35). If you
really want to drag the formula down and just have the same average value for
every row of the same corresponding hour, you need to put $ (dollar signs)
around your range to make it an Absolute Reference (i.e. change the function
in C2 to =AVERAGE(IF(HOUR($A$2:$A$34)=6,$B$2:$B$34,"")) ). That way when you
drag the function down from C2 to C3 the range will stay the same.

Hopefully that clears up the confusion in what you are seeing.

 

[Bob Phillips]

Try this

=IF(HOUR(A2)<>HOUR(A1),AVERAGE(IF(HOUR($A$2:$A$500)=HOUR(A2),$B$2:$B$500,"")
),"")

still array-entered

 

[kk]

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




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.

 

[Guest]

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

 

[Guest]

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.