Using ONLY the data filtered

[Fred Zack]

Is there any way to do statistics on ONLY the data SHOWING in a filtered
spreadsheet and not the entire file (hidden data and all). I dont mean using
the SUBTOTAL function that only allows those specific ten or so I want to do
Regression, T-tests, etc.

In fact what is the point of filtering data if statistics applied to it are
going to be applied to all the data?

[Jerry W. Lewis]

For worksheet functions that support missing values, you could write an array
formula using a VBA function like IsVisible
http://groups.google.com/group/micro...39e348e139e1bc
to select only the visible data.

Jerry

"Fred Zack" wrote:

> Is there any way to do statistics on ONLY the data SHOWING in a filtered
> spreadsheet and not the entire file (hidden data and all). I dont mean using
> the SUBTOTAL function that only allows those specific ten or so I want to do
> Regression, T-tests, etc.
>
> In fact what is the point of filtering data if statistics applied to it are
> going to be applied to all the data?

[claytorm]

Fred,
I have the same problem. Did you find a solution without having to resort to VBA?

I my case, I am running a series of screens on stocks, and then running numerous tests on the filtered data. Incorporating VBA into each of these tests is not feasable.

Bert.

[Bruce Sinclair]

In article <[email protected]>, claytorm <[email protected]> wrote:
>
>Fred,
>I have the same problem. Did you find a solution without having to
>resort to VBA?
>
>I my case, I am running a series of screens on stocks, and then running
>numerous tests on the filtered data. Incorporating VBA into each of
>these tests is not feasable.

This may be over simplistic for what you are doing, but if you filter the
data, you can then cut and paste it into (say) another sheet and it will
copy only the data that is showing (ie your filtered data). Could you then
apply your macros to this ?



Bruce

----------------------------------------
I believe you find life such a problem because you think there are the good
people and the bad people. You're wrong, of course. There are, always and
only, the bad people, but some of them are on opposite sides.

Lord Vetinari in Guards ! Guards ! - Terry Pratchett

Caution ===== followups may have been changed to relevant groups
(if there were any)

[claytorm]

Bruce,

Thanks. This is actually just what I am doing at the moment. The problem is every time the filter criteria is changed the whole process has to be repeated, which is laborius as the filtered data is approx. 4000 rows * 25 cols. Any other ideas?

Bertie.

[Carim]

Hi,

Take a look at the function Subtotal() ... it does excatly what you are looking for ...

HTH
Cheers
Carim