That worked! I was using the =percentile, not rankpercentile.

I am a little confused what the subtotal does because all the values become
1. How does that help the percentile work?

I really appreciate the solution!


"ShaneDevenshire" wrote:

Hi again,

Here's the basic idea: add a new column to your table with the formula
SUBTOTAL(2,A2)
where A2 is the first cell for which you are calculating the PERCENTILE.
Copy this formula down to the bottom of the spreadsheet.

Replace your PERCENTILE formula with the array formula:

=PERCENTILE(IF(B$2:B$25=1,A$2:A$25,""),0.2)

Here B2:B25 is the column containing the SUBTOTAL function above and the
range A2:A25 is the numbers you are calculating the PERCENTILE on. My
suspicion is that you are using the RANKPERCENTILE function rather than
PERCENTILE but until I know the above solution works. The 0.2 means that you
want the value of the 20th percentile. Note this formula is entered using
Shift Ctrl Enter, since it is an array.
--
Cheers,
Shane Devenshire


"Kaspr" wrote:

I have a table with 7 columns and 60,000 rows. When I filter column 5 (finite
number of different values), columns 6 & 7 have averages and percentiles set
for them at the bottom.

2 problems:
1. Selecting a filter loses the averages and %'s (I cut and move to the top
with a freeze on the panes, seems to fix this).
2. The averages and %'s do not adjust to the data now displayed. Out of
60,000 rows, around 8,000 are visible, but it is still averaging all of them.

How do I get the averages and %'s to self adjust.