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

 

[Aladin Akyurek]

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?

Your specs are confusing, it looks more like...

=IFERROR(AVERAGEIF(OFFSET(INDEX(B:B,SUM(ROW())-1),0,0,
  -MIN(30,SUM(ROW())-1)),">0"),"")

if you are entering the formula after the row of the last value.

Outside the data column:

=IFERROR(AVERAGEIF(OFFSET(INDEX(B:B,MATCH(9.99999999999999E+307,B:B)),0,0,
  -MIN(30,COUNT(B:B))),">0"),"")

 

[aaacccc]

matty, i want it to select the last 30 rows though, because i will be adding new rows often

 

[aaacccc]

Aladin, what I am trying to do is select the last 30 rows automatically, and then within those 30 rows take the average of the nonzero values

 

[Aladin Akyurek]

Aladin, what I am trying to do is select the last 30 rows automatically, and then within those 30 rows take the average of the nonzero values

Try the formulas I posted and pick out the one you consider appropriate.

 

[aaacccc]

I have found a formula that works, thank you all for your help!

 

[Aladin Akyurek]

I have found a formula that works, thank you all for your help!

That's great. Just for the record:

Given...
1
0
6
8

1) The average of nonzero values in the last three used cells is: 7

2) The average of the last three nonzero values is: 5

The formulas I posted targets (1), not (2), after your answer to a question posed earlier in this thread.

 

[aaacccc]

thanks aladin, I was trying to do the first thing that you described

 

[Aladin Akyurek]

thanks aladin, I was trying to do the first thing that you described

Ok. Here are those formulas:

https://dl.dropboxusercontent.com/u/65698317/aaAverageNonZeroValuesOfTheLastNCells%20Public%20aaacccc.xlsx

 

[markmzz]

Another way:

=AVERAGEIF(OFFSET(D326,,,-30),"<>0")

Markmzz

 

[markmzz]

Another way:

=AVERAGEIF(OFFSET(D326,,,-30),"<>0")

Markmzz

A small modification (array formula - use Ctrl+Shift+Enter and not only Enter):

With the cell D327 active, create the name LastData =D326 (not =$D$326).

=AVERAGEIF(OFFSET(LastData,,,-30),"<>0")

or use

=AVERAGEIF(OFFSET($D$327,-1,,-30),"<>0")

Markmzz