Help needed with index (offset) formula

[Guest]

I have a column with data in cells A1 down to A20 and with data being added each day to the bottom. I am trying to calculate the 'average of the last five cells' (so today it would be the average of A16:A20, whereas tomorrow it would be average of A17:A21). I know I need to use the index/offset formula but can't seem to get it right - can anyone help me from tearing my hair out!

Thanks

 

[Aladin Akyurek]

On 2002-03-14 04:39, Anonymous wrote:
I have a column with data in cells A1 down to A20 and with data being added each day to the bottom. I am trying to calculate the 'average of the last five cells' (so today it would be the average of A16:A20, whereas tomorrow it would be average of A17:A21). I know I need to use the index/offset formula but can't seem to get it right - can anyone help me from tearing my hair out!

Thanks

=AVERAGE(OFFSET($A$1,MATCH(9.99999999999999E+307,A:A)-5,0,5,1))

You can also put 5 in some cell, e.g., E1, and use:

=AVERAGE(OFFSET($A$1,MATCH(9.99999999999999E+307,A:A)-E1,0,E1,1))

 

[Derek]

Hi

Provided Column A is dedicated solely to your list, with data starting in A1 and with no gaps, you can use these 2 formulas
Formula in B1 is:... ="A"&COUNTA(A:A)-4&":"&"A"&COUNTA(A:A)
Formula in C1 is:... =AVERAGE(INDIRECT(B1))

Hope this helps
regards
Derek

 

[Guest]

Thanks - all work fine!