average nonzero values within the last 30 entries

[aaacccc]

I want to average the last non zero values that fall within the last 30 entries. Entries will be added regularly.

I also want to know if there is a way to re-display the values of the last 30 rows in another table within the same sheet and if it can update automatically as new rows are added.


the equation I have is: but I do not how to change it. Right now it gives me the average of the last 30 non zero values of the column regardless of whether it lies in the last 30 entries.


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

 

[LxQ]

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

Ctrl+Shift+Enter to confirm.... this just takes out the IF function, which was used to look at the rows and consider them if they were non-zero..

 

[Aladin Akyurek]

See:

http://www.mrexcel.com/forum/excel-...zero-entries-column-update-automatically.html

 

[aaacccc]

LxQ, your formula seems to be working, thank you very much for your help!