Forum Discussion

lbeamer's avatar
lbeamer
Copper Contributor
Nov 11, 2021

Input a number in a cell for example cell M2 that will be used in a for a formula in a column K.

The formula in column K is =Average (D2:D7).  Currently I copy this all the way down 6000 rows of column K.  But if I want to change the Average to 10 rows.  I have to go and change the formula in co...
excel
Formulas and Functions
mtarler's avatar
mtarler
Silver Contributor
Nov 11, 2021
you need to either use something like OFFSET or INDIRECT
=Average(OFFSET(D2,0,0,$M$2))
=Average(INDIRECT("D"&row(D2)&":D"&(row(D1)+$M$2)))
  • mathetes's avatar
    mathetes
    Gold Contributor
    Nov 11, 2021

    mtarler lbeamer 

    And one more variation:  =AVERAGE(D1:INDIRECT("D"&TEXT(ROW(D1)+$M$2,"0"))) copied down....

Resources