How to modify cell references using formulas

[ehercun]

Hi there,

I'm trying to build a model that will modify the cell references dynamically. I tried to play around with this manually and did some searching and could not find what I was after.

Basically what I'm trying to do is if I'm for instance trying to calculate the average of the last 150 cells (which will by dynamic, ie. cell reference locked to a box say, E10, where I can type 150 or 200 if I prefer), and then simply subtract 150 from the current row. This probably hardly makes sense, so the following might help:

I'm in in row 170, column A. If i wanted to calculate the last 150 cells above, how can I specify in =average(A170:A(170-$E$10))) so that the range is dynamically updated based on the reference to E10. So, if I wanted to change 150 to 120 for instance, the formulas will automatically calculate the averages of the last 120 cells.

Thoughts? Does this make sense?

 

[MrKowz]

Welcome to the forums!

Try:

=AVERAGE(OFFSET(A170,0,0,-$E$10))

 

[JoeMo]

Another way is to enter the address of the last cell to include in E10. So in E10 you could enter, for example, A170:

=AVERAGE(A1:INDIRECT($E$10))

 

[ehercun]

Sick!! Thanks for the super quick response.

Much appreciated. Have a great day.

 

[MrKowz]

Sick!! Thanks for the super quick response.

Much appreciated. Have a great day.

Happy we were able to find a working solution for you. Thanks for the feedback!