75th percentile of filtered data

[Excel1991]

I have a set of data in an excel spreadsheet that I have filtered. I would like to find the 75^th percentile of some of the data, but I do not want to pickup the “hidden/filtered” rows in the data set.Does anyone know a formula that circumvents this? Thank you!

 

[mse330]

Hello

Try the aggregate function like below … Change to suit your data set

=AGGREGATE(16,5,B2:B9,0.75)

 

[Excel1991]

Thank you this works! Do you happen to know how to use this formula to avoid "0's" as well?

 

[mse330]

You are welcome ... I am not really sure how to avoid zeros with the aggregate function perhaps some other members come up with an answer

 

[jsparrow22]

Hello

Try the aggregate function like below … Change to suit your data set

=AGGREGATE(16,5,B2:B9,0.75)

I was experiencing the same issue. This solution has helped me out so much. Thank you.

 

[TiredHamster]

You are welcome ... I am not really sure how to avoid zeros with the aggregate function perhaps some other members come up with an answer

To avoid zeros and ones in your percentile, you can use PERCENTILE.EXC function.