3

This is a question about Excel, not VBA. I have a simple formula in my top row that averages the ten cells below it. I want to copy the row below this one and insert a copy as a new row. (My range is most recent first and older ones fall out of the most recent ten eventually.)

When I copy and paste the row, the formula in the row above adjusts itself to ignore the row I inserted (it averages ten cells below the new row.) Why does the formula not "stick" to the 2nd through 11th rows? How do I make it do that?

For example, if my spreadsheet looked something like this (I do not have it with me):

A1 Average(A2:A11)  
A2 5  
A3 8  
...  
A11 3  
A12 (unused)  

and I copy row A2 and insert it there, the formula in A1 becomes: Average(A3:A12). I want it to stay with A2:A11. I tried putting in dollar signs (A$2:A$11) and the insert still changed the formula. Please help.

1 Answer 1

3

Try:

=AVERAGE(INDIRECT("A2:A11"))
2
  • Thank you. Is INDIRECT a new-ish function? I don't recall learning it in versions up to 2007... I don't understand why there is not another way to make a range be "absolute", and how even making it Absolute, is not. Strange that we have to "hide" the formula from Excel so that it does not try to be helpful! As it is, Excel incessantly warns me that my chosen range does not include adjacent values. Well, that is what I mean by taking the most recent ten! Isn't this a fairly common thing to do?
    – user488805
    Oct 12, 2015 at 14:40
  • Accepting, as this is the only answer after a day, and apparently the only possible answer. Still perplexed that taking a subset of data is not something that Excel suffers gladly. Still wondering why I appear to be the first person to ask about this on this huge site?
    – user488805
    Oct 13, 2015 at 16:10

You must log in to answer this question.