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

excel
Formulas and Functions

2 Replies

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

Resources