Thanks:  0
Likes:  0

# Thread: Help needed with index (offset) formula

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

2. 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))

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

4. Thanks - all work fine!

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•