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

Occasional Contributor

# 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 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

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

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

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

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