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

Copper Contributor

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 column K to = Average (D2:D12) and copy it down 6000 rows.  I would prefer to change cell M2 from 5 to 10 and have Column K automatically recalculate the average based on 10 rows instead of 5.

this formula gives me an error.  What am I doing wrong?   =Average (D2:D2+$M$2)

Thank you in advance for replies

2 Replies
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)))

@mtarler @lbeamer 

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