How to modify cell references using formulas

ehercun

New Member
Joined
Jul 27, 2017
Messages
2
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?
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Welcome to the forums!

Try:

=AVERAGE(OFFSET(A170,0,0,-$E$10))
 
Last edited:
Upvote 0
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))
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top