Try this...

No error checking!

If there aren't 100 visible rows then you'll get an error.

Array entered** :

=AVERAGE(IF(ROW(Data)=LARGE(IF(SUBTOTAL(3,OFFSET(D ata,ROW(Data)-MIN(ROW(Data)),0,1))*ROW(Data),ROW(Data)),TRANSPOS E(ROW(INDIRECT("1:100")))),Data))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:9c43d5194b400@uwe...
Hi All,

I have a dynamic named range "Data" that spans one column and many rows;
"Data" houses numeric values.

Without copying or moving cells: Is it possible to have a single formula
to
average Data's last 100 cells of filtered visible data?

Your assistance is very much appreciated.

Thank you,
Sam

--
Message posted via http://www.officekb.com