Forum Discussion

irvine93's avatar
irvine93
Copper Contributor
Aug 01, 2019

How to run a 24-cell block formula

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.

excel

2 Replies

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    Aug 01, 2019
    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))
  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Aug 01, 2019

    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 

Resources