# 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. ## 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. ## 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. ## 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. ## 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. ## Re: average last 30 non zero entries in a column, update automatically

I have tried this and it returns: #VALUE!

6. ## 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. ## Re: average last 30 non zero entries in a column, update automatically

Originally Posted by aaacccc
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. ## 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. ## 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. ## 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 Last