Page 1 of 2 12 LastLast
Results 1 to 10 of 20

average last 30 non zero entries in a column, update automatically

This is a discussion on average last 30 non zero entries in a column, update automatically within the Excel Questions forums, part of the Question Forums category; I have several columns and at the end of the column I need to average the last 30 non zero ...

  1. #1
    New Member
    Join Date
    Jul 2013
    Posts
    19

    Red face average last 30 non zero entries in a column, update automatically

    I have several columns and at the end of the column I need to average the last 30 non zero values. So far I have this:

    =SUM(OFFSET(D16,COUNTA(D16:D326)-29,0,30,1))
    column starts at D16 and ends D326

    it seems to be working, but it automatically selects the last 30 entries regardless of the value, there are many cells that are empty and so I made them zero. I want an average of the last 30 values that are not zero. I will be adding rows to the column and so I want the formula to update as I add more entries.

    I do not know much about excel, and keep reading online about arrays and codes, the simplest way would be best, thanks!
    ex:

    D
    .
    .
    .
    16
    .
    .
    .
    326
    Average here

  2. #2
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,232

    Default Re: average last 30 non zero entries in a column, update automatically

    Try:

    Code:
    =AVERAGE(IF(ROW(A1:A100)>=LARGE(IF(A1:A100,ROW(A1:A100)),30),IF(A1:A100,A1:A100)))
    Committed with CTRL+SHIFT+ENTER.

    Matty

  3. #3
    New Member
    Join Date
    Jul 2013
    Posts
    19

    Default Re: average last 30 non zero entries in a column, update automatically

    matty, thank you for your help, do I put the code in the cell or does it go elsewhere and when do I enter the CTRL+SHIFT+ENTER, I am really not very familiar with excel

  4. #4
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,232

    Default Re: average last 30 non zero entries in a column, update automatically

    Yes, the formula goes in the cell where you want the result to be returned. It should reference the range containing your data (i.e. change A1:A100 to where your data actually resides).

    As the formula uses IF within an array, it needs to be committed with CTRL+SHIFT+ENTER to work. If done correctly, curly brackets {} will surround the formula when seen in the formula bar.

    Matty

  5. #5
    New Member
    Join Date
    Jul 2013
    Posts
    19

    Default Re: average last 30 non zero entries in a column, update automatically

    I have tried this and it returns: #VALUE!

  6. #6
    New Member
    Join Date
    Jul 2013
    Posts
    19

    Default Re: average last 30 non zero entries in a column, update automatically

    I have =SUMIF(B1:B150,">0")/COUNTIF(B1:B150,">0") which I am using at the end of the column so that it gives me an everage of only the non-zero values and I came up with =AVERAGE(OFFSET(B1,COUNTA(B1:B150)-31,0,30,1)) which gives me the sum of the last set of 30 values, but I cannot figure out how to get the average for the last 30 non-zero entries

  7. #7
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,232

    Default Re: average last 30 non zero entries in a column, update automatically

    Quote Originally Posted by aaacccc View Post
    I have =SUMIF(B1:B150,">0")/COUNTIF(B1:B150,">0") which I am using at the end of the column so that it gives me an everage of only the non-zero values and I came up with =AVERAGE(OFFSET(B1,COUNTA(B1:B150)-31,0,30,1)) which gives me the sum of the last set of 30 values, but I cannot figure out how to get the average for the last 30 non-zero entries
    The formula I provided will work, as long as you commit it with CTRL+SHIFT+ENTER. The error you're getting is because you have only committed it with ENTER.

    Matty

  8. #8
    New Member
    Join Date
    Jul 2013
    Posts
    19

    Default Re: average last 30 non zero entries in a column, update automatically

    ok, now it works perfeclty, thank you for your help and time

  9. #9
    New Member
    Join Date
    Jul 2013
    Posts
    19

    Default Re: average last 30 non zero entries in a column, update automatically

    I guess initially, I wanted the average of the last 30 non zero values, but what I actually need is the average of the non zero values within the last 30 entries only. How does that change the formula that I have already?

  10. #10
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,232

    Default Re: average last 30 non zero entries in a column, update automatically

    I guess initially, I wanted the average of the last 30 non zero values, but what I actually need is the average of the non zero values within the last 30 entries only. How does that change the formula that I have already?
    Sounds like:

    Code:
    =AVERAGEIF(B71:B100,">0",B71:B100)
    Matty

Page 1 of 2 12 LastLast

Tags for this Thread

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