LinkBack URL
About LinkBacks
I’m looking for help in a formula that will calculate the percentile of only the visible cells data in a column after applying a filter.
Nothing I have been able to do works and it continues to calculate the hidden cells.
I’m looking for help in a formula that will calculate the percentile of only the visible cells data in a column after applying a filter.
Nothing I have been able to do works and it continues to calculate the hidden cells.
Hi, @thomascmartiniv!
If your range of numbers is A2:A10, you can try this Array Formula (Must be entered with Ctrl + Shift + Enter, and not just Enter):
=PERCENTILE.EXC(IF(SUBTOTAL(102,OFFSET(A2,ROW(A2:A10)-ROW(A2),)),A2:A10),0.5)
This could be an option for PERCENTILE 50. Blessings!
It can also be done with AGGREGATE. It does not have to be array entered. For median.Formula:
Please Login or Register to view this content.
Dave
Wow, excellent support from this forum! I tried both methods but using flamesretired Aggregate formula worked the way I needed. As I toggled through different filters in my raw data it calculated the percentile result as it should!
Thanks again all for the contributions.
You're welcome. Thanks for the feedback.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Just found this thread after my office analysts told me this topic couldn't be done. The AGGREGATE formula worked like a champ. The whole office was mesmerized by my excel prowess and my analysts learned something new today. Thanks for taking the time to post the solution.
Another happy camper. Thanks FlameRetired!
You are welcome. Glad it helps. Thank you for the feedback.
.. and another one.. Thanks FlameRetired!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks