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

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

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

T. Valko wrote:
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)),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.

--
Message posted via http://www.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 wrote:
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)),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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1

incorrectly gave credit to Max.

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

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

--
Biff
Microsoft Excel MVP


"Sam via OfficeKB.com" <u4102@uwe wrote in message
news:9c4634c1c84b3@uwe...
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 wrote:
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)),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.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200909/1