Repeating sum

Posted by J. Glasgow on January 08, 2001 7:46 AM

I have fifteen minute data and want to sum them into hourly data. I need a formula that when i drag or do an autofill will take the sum of every four cells( i.e. the next hour) and not the next four cells( shifted down one cell.)

Posted by Mark W. on January 08, 2001 9:08 AM

If your values are in column A:A starting with
cell A1, enter =IF(MOD(ROW(A1),4),"",SUM(A1:A4))
into cell B1 and copy down.

Posted by Tim Francis-Wright on January 08, 2001 9:14 AM

Here's one way to do it.

Let's assume that the times are in A1:A100,
and the figures are in B1:B100.

Set C1 to be the following array formula
(use control-shift-enter instead of enter):-
=SUM(IF(HOUR($A$1:$A$100)=HOUR(A1),$B$1:$B$100))

This will give the totals for the current hour.
It will not work for data over more than one
day--but you could use something like:
=SUM((HOUR($A$1:$A$100)=HOUR(A1))*(INT($A$1:$A$100)=INT(A1))*($B$1:$B$100))
to do that.

Good luck!

Posted by Mark W. on January 08, 2001 10:20 AM

Actually, this is better behaved...

Enter =IF(MOD(ROW(A1),4),"",SUM(OFFSET(A1,,,-4)))
into B1 and copy down.