average every 5 values

K

kendal

How do I average every 5 values in a column (a1:a9115)
my hand is getting cramps.
 
J

JE McGimpsey

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(MOD(ROW(A1:A9115),5)=1,A1:A9115,""))

will average A1, A6, A11, A16...

change the =1 to

=0 for A5,A10,A15...
=2 for A2,A7,A12...
=3 for A3,A8,A13...
=4 for A4,A9,A14...
 
J

JE McGimpsey

One way:

B1: =AVERAGE(OFFSET($A$1,(ROW()-1)*5,0,5,1))

Copy down as far as necessary.
 
Top