Any math function on last 5 non zero or empty cells in a column

[John Clipfell]

I am looking for a way to perform mathematical functions (sum, average etc)
on the last X non-zero or non-blank entries in a list. For the sake of the
example assume the mathematical function will reside in column B and will
start after there are enough filled data sets. I need to figure out how to
test for non-zero or non-blank cells starting from the bottom of a column and
working upward until X have been found.

Example (x=5)
A1= 10; B1=(na)
A2= 12; B2=(na)
A3= 11; B3=(na)
A4= 10; B4=(na)
A5= 13; B5=AVERAGE A1:A5 (11.2)
A6= 0 or ""; B6=AVERAGE A1:A5 (11.2)
A7= 14; B7=AVERAGE A2:A7 (12.0)
A8= 14; B8=AVERAGE A3:A8 (12.4)
A9= 0 or "" B9=AVERAGE A3:A8 (12.4)
A10= 9; B10= AVERAGE A:A10 (12.0)

 

[Domenic]

SUM(SUBTOTAL(9,OFFSET(A1,LARGE(IF(A1:A10<>0,ROW(A1:A10)),ROW(INDIRECT("1:
"&B1)))-CELL("row",A1),0)))

and

AVERAGE(SUBTOTAL(9,OFFSET(A1,LARGE(IF(A1:A10<>0,ROW(A1:A10)),ROW(INDIRECT
("1:"&B1)))-CELL("row",A1),0)))

....entered using CONTROL+SHIFT+ENTER, and where B1 contains the last X
number of non-zero numerical values to be added or averaged.

Hope this helps!