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

[aaacccc]

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

 

[Matty]

Try:

=AVERAGE(IF(ROW(A1:A100)>=LARGE(IF(A1:A100,ROW(A1:A100)),30),IF(A1:A100,A1:A100)))

Committed with CTRL+SHIFT+ENTER.

Matty

 

[aaacccc]

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

 

[Matty]

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

 

[aaacccc]

I have tried this and it returns: #VALUE!

 

[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

 

[Matty]

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

 

[aaacccc]

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

 

[aaacccc]

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?

 

[Matty]

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:

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

Matty