Determine Quartile ignoring filtered out data AND with the right name in a different collumn

[dwg83]

I have scoured mrexcel, now I am posting a fresh thread. I have values in column O that I would determine quartiles for. My table will be filtered. However it won't be filtered by name which is in column A. How do I have excel tell me the quartile of the values showing that have the correct name in column A? Closest I have gotten:

{=QUARTILE.INC(IF(AND($A$13:$A$2080=$S$2,SUBTOTAL(2,OFFSET($O$13:$O$2080,ROW($O$13:$O$2080)-ROW(O13),,1))),$O$13:$O$2080),3)}

Thanks in advance!

 

[Domenic]

Try...

=QUARTILE.INC(IF(SUBTOTAL(2,OFFSET($O$13:$O$2080,ROW($O$13:$O$2080)-ROW(O13),,1)),IF($A$13:$A$2080=$S$2,$O$13:$O$2080)),3)

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[dwg83]

Thank you, it does!

 

[Domenic]

You're very welcome!

Cheers!

 

[dwg83]

Great

 

[dwg83]

Domenic, would the average of that same kind of data set be possible too? I know I wouldn't be using Quartile, but should I still use subtotal? Not sure how I would incorporate the extra "name" parameter.

thanks,
David

 

[dwg83]

Found answer here: http://www.mrexcel.com/forum/excel-questions/607585-subtotal-averageif.html