Average Last 100 Filtered Visible rows

[Sam via OfficeKB.com]

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

 

[T. Valko]

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(Data,ROW(Data)-MIN(ROW(Data)),0,1))*ROW(Data),ROW(Data)),TRANSPOSE(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.

 

[Sam via OfficeKB.com]

Hi Max,

Absolutely Brilliant! I tried many, many variations with the AVERAGE(IF
(SUBTOTAL(2,OFFSET(... but really could not fathom it!. Most appreciated.
Thank you so very much.

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Biff,

Apologies, incorrectly gave credit to Max. I was looking at a previous Post
that Max had answered. Sorry!

Absolutely Brilliant! I tried many, many variations with the AVERAGE(IF
(SUBTOTAL(2,OFFSET(... but really could not fathom it!. Most appreciated.
Thank you so very much.

Cheers,
Sam

 

[T. Valko]

incorrectly gave credit to Max.

Max doesn't get enough credit for what he does!

You're welcome, Sam. Thanks for the feedback!