Quartile of Filtered List

P

Perry Schneider

New Member
Joined
Sep 17, 2005
Messages
2
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 need the 3rd quartile for "Q2:Q7345" when filtered. This median formula {=IF(SUBTOTAL(9,Q2:Q7345)=0,0,MEDIAN(IF(SUBTOTAL(9,OFFSET(Q2:Q7345,ROW(Q2:Q7345)-MIN(ROW(Q2:Q7345)),,1))>0,Q2:Q7345)))} works fine on a filtered list.

Any help would be appreciated.

Thanks.

Perry
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
Perry Schneider said:
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 need the 3rd quartile for "Q2:Q7345" when filtered. This median formula {=IF(SUBTOTAL(9,Q2:Q7345)=0,0,MEDIAN(IF(SUBTOTAL(9,OFFSET(Q2:Q7345,ROW(Q2:Q7345)-MIN(ROW(Q2:Q7345)),,1))>0,Q2:Q7345)))} works fine on a filtered list.

Any help would be appreciated.

Thanks.

Perry
Click to expand...

I take it that Q2:Q7345 is the range interest from the data area to which you apply auto-filter... Try one of:

=PERCENTILE(IF(SUBTOTAL(3,OFFSET(Q2:Q7345,ROW(Q2:Q7345)-ROW(Q2),,1)),Q2:Q7345),0.75)

=QUARTILE(IF(SUBTOTAL(3,OFFSET(Q2:Q7345,ROW(Q2:Q7345)-ROW(Q2),,1)),Q2:Q7345),3)

The formula you pick out must be confirmed with control+shift+enter, not just with enter.
 
Upvote 0
Thank you for this answer. I have a variation I need help with if you would. In addition to ignoring the filtered out rows, I need to check if the value (first name) in a different column is equal to the name I would like. The result being a quartile calculation of the filtered data with the correct name. Ideas? I've tried this so far and it hasn't worked.

{=QUARTILE(IF(AND(SUBTOTAL(3,OFFSET(O13:O2080,ROW(O13:O2080)-ROW(O13),,1)),OFFSET(O13:O2080,,-13)=$S$2),O13:O2080),3)}
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
319
ItalianPlatinum
I
M
  • Question
Excel script Cope paste with filters
Replies
0
Views
232
michalina
M
john Smith72
  • Solved
Auto filter VBA
Replies
2
Views
162
john Smith72
john Smith72
A
  • Solved
Loop through filtered data VBA
Replies
9
Views
574
Peter_SSs
Peter_SSs
J
  • Question
Cannot close temporary file and delete in VBA for Excel coding
Replies
2
Views
555
jolor69
J

Forum statistics

Threads
1,216,058
Messages
6,128,538
Members
449,456
Latest member
SammMcCandless

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top