QUARTILE Function on Sorted Data

[Gerry Pat]

I am trying to utilize the QUARTILE function on a series of investment
returns. When utilizing the complete data set (returns on 195 funds),
Quartile works fine. However, when I use Autofilter to focus on returns say
in Asia (returns on 21 funds), Quartile returns the same results as if using
the complete data set (all 195 funds). How does one get the QUARTILE
function to ignore the hidden values sorted out by the use of Autofilter? Or
do I need to take a different approach?

Thanks in advance for the help. Gerry

 

[Domenic]

Assuming that B2:B10 contains the data, try the following formula that
needs to be confirmed with CONTROL+SHIFT+ENTER...

=QUARTILE(IF(SUBTOTAL(3,OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,1)),B2:B10),2
)

Adjust the range and quart, accordingly.

Hope this helps!

 

[Gerry Pat]

Thanks! It seems to be working very well. Now to test it on several
thousand returns. Again, thanks.