Using ONLY the data filtered

[Guest]

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?

 

[Guest]

For worksheet functions that support missing values, you could write an array
formula using a VBA function like IsVisibl
http://groups.google.com/group/microsoft.public.excel.programming/msg/c639e348e139e1bc
to select only the visible data.

Jerry

 

[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]

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

 

[Carim]

Hi,

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

HTH
Cheers
Carim