Subtotal with quartile function

[ManalEL]

Hello,
I'm working on a spreadsheet that computes a number of statistical results, including quartile functions. I want to be able to filter the rows and use only the rows that are NOT hidden within the calculations. I would typically use the SUBTOTAL function, but it does not include the quartile function in it's options.
Is there any way to figure quartile calculations on only non-hidden rows?
Thanks!

Manal

 

[James006]

Hi,
Did you see this thread from Aladin :

Quartile of Filtered List

Hello, Have a question like the "Tracking visible cells from an autofiltered list" post; but can't adapt the answer {=QUARTILE(IF(SUBTOTAL(3,OFFSET(B2:B12,ROW(A1:A11)-1,,1)),B2:B12),2)} to work for my filtered list. Just need to get the 75th Percentile when I filter my database. For example, I...

www.mrexcel.com

 

[Domenic]

Assuming that A2:A100 contains the data, here's another way

=AGGREGATE(17,7,A2:A100,1)

Note that the first argument in the AGGREGATE function is the function number, which specifies which function to use. In this case, the number 17 specifies QUARTILE.INC. To specify QUARTILE.EXC, replace 17 with 19. Adjust the range accordingly.

For additional information regarding the AGGREGATE function, have a look at the following article...

AGGREGATE function - Microsoft Support

The AGGREGATE function can apply different aggregate functions to a list or database with the option to ignore hidden rows and error values.

support.microsoft.com

Hope this helps!