Hi All,

I understand from some other posts that there is a way I can get some
functions to work when using the auto filter feature on a spreadsheet
filled with data. I have such a spreadsheet, a number of them actually,
that I'd like to be able to see the filtered rows and have statistics
display for the filter that is applied at the time. It is the
statistical functions that are giving me the hard time.

Let's say my spreadsheet has these columns and some of this data:

Symbol ProfitFactor GrossProfit GrossLoss NetProfit
WinLossRatio ... ... ...
==============================================
AAPL 1.6 20005 8000 12005
68.3 ... ... ...
MSFT 1.9 10000 9000 1000
25.3 ... ... ...
BBY 1.5 30500 100 30400
88.0 ... ... ...
....
....
....

And below this is a set of statistical functions for the whole set of
500+ rows. These work fine when I don't need to filter. I'd like to get
these (or a similar set of functions) to work when I've applied various
filters to them.

Avg 1.7 20168 5700 14468
60.5 ... ... ...
Min 1.5 10000 100
1000 25.3 ... ... ...
Max 1.9 30500 9000 30400
88.0 ... ... ...
Count 3 3 3
3 3 ... ... ...
St.Dev. 0.2 10000 4100 12000
35.0 ... ... ... (not real values)


Can someone show me how I can use each of these functions with hidden
rows and variable auto filter criteria? Sometimes I'll set criteria in
three or four of the columns at a time, if this makes a difference in
the solution.

Thanks,
Tracy

Hi!

See Help on the SUBTOTAL function. It can be used for all of the functions
you have listed.

Biff

"tracy" wrote in message
oups.com...
Hi All,

I understand from some other posts that there is a way I can get some
functions to work when using the auto filter feature on a spreadsheet
filled with data. I have such a spreadsheet, a number of them actually,
that I'd like to be able to see the filtered rows and have statistics
display for the filter that is applied at the time. It is the
statistical functions that are giving me the hard time.

Let's say my spreadsheet has these columns and some of this data:

Symbol ProfitFactor GrossProfit GrossLoss NetProfit
WinLossRatio ... ... ...
==============================================
AAPL 1.6 20005 8000 12005
68.3 ... ... ...
MSFT 1.9 10000 9000 1000
25.3 ... ... ...
BBY 1.5 30500 100 30400
88.0 ... ... ...
...
...
...

And below this is a set of statistical functions for the whole set of
500+ rows. These work fine when I don't need to filter. I'd like to get
these (or a similar set of functions) to work when I've applied various
filters to them.

Avg 1.7 20168 5700 14468
60.5 ... ... ...
Min 1.5 10000 100
1000 25.3 ... ... ...
Max 1.9 30500 9000 30400
88.0 ... ... ...
Count 3 3 3
3 3 ... ... ...
St.Dev. 0.2 10000 4100 12000
35.0 ... ... ... (not real values)


Can someone show me how I can use each of these functions with hidden
rows and variable auto filter criteria? Sometimes I'll set criteria in
three or four of the columns at a time, if this makes a difference in
the solution.

Thanks,
Tracy

Thank you Biff. I just looked much to my chagrin. Quite a versitle and
ill named function.

Tracy