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.

I figured out the averages by using a subtotal(1,x,x) but still haven't
figured out the percentile. Any help would be greatly appreciated!


"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.

If you are using the Percentile function to make the calculation it does not
support "Visible cells only calculations". Before I try for a solution can
you show me how you are calculating percentiles?


--
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.

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.

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.

This really is not a percentile issue, the key here is to do a PercentileIF,
but since there is no such function we need to devise one. Your if is "IF
the row is not hidden", so we need to identify non-hidden rows. The SUBTOTAL
function allows us to do that. The SUBTOTAL(2,A2) function allows us to do
this since one extremely important feature of this function is it works on
visible cells only (visible from the standpoint of rows hidden by
AutoFilter). Normally we look at a range and ask the subtotal function to
sum or count the visible cells only, but here I refer to only one cell, the
cell on the same row. So if the row is visible this function returns 1 if it
is hidden it returns 0.

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

Since column B in my formula is where the SUBTOTAL functions are located the
IF statement returns the values in the range A2:A25 IF the cell corresponding
cell in column B has a 1, if it has a 0 it returns "". So the PERCENTILE
function see something like


PERCENTILE("","",23,65,"",56,87,"")

Since "" means blank, and this function ignores blanks it only works with
visible cells. The blanks are cells on the hidden rows as determined by the
SUBTOTAL function.

This formula must be an array because IF normally deals with only one cell.

I hope this helps.

Cheers,
Shane

--
Thanks,
Shane Devenshire


"Kaspr" wrote:

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.