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