How to run a 24-cell block formula

Copper Contributor

Good morning, I have a small problem and I wanted to know if anyone can support me. What I need is for my Excel formula to take 24 cells to get an average and to drag it take the next 24 cells, that is, if I take from cells 1 to 24 when dragging my formula take it from cell 25 to 48. I would appreciate it Much your support.

2 Replies

Hi @irvine93 

 

Please use below formula, it will give average of numbers with the gap of 24 rows, for example 1st it will give average of 1 - 24 row, when you drag it will give average from 25 to 48 and so on.

 

=AVERAGE(OFFSET($C$5:$C$244,((ROW(C5)-ROW($C$5))*24),0,1,1):OFFSET($C$5:$C$244,((ROW(C5)-ROW($C$5))*24),0,24,1))

 

Excel file is also attached for your reference.

 

Hope it will hep you

Thanks

Tauqeer 

Assuming your values are in Column A, a shorter non-volatile formula, starting in B1, is:
=AVERAGE(INDEX(A:A,ROW()*24-24+1):
INDEX(A:A,ROW()*24))