Help needed with index (offset) formula
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Help needed with index (offset) formula

  1. #1
    Guest

    Default

     
    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. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,864
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    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. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    Guest

    Default

      
    Thanks - all work fine!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com