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?

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?

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.


--
claytorm
------------------------------------------------------------------------
claytorm's Profile: http://www.excelforum.com/member.php...o&userid=11610
View this thread: http://www.excelforum.com/showthread...hreadid=529820

In article , claytorm 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)

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.


--
claytorm
------------------------------------------------------------------------
claytorm's Profile: http://www.excelforum.com/member.php...o&userid=11610
View this thread: http://www.excelforum.com/showthread...hreadid=529820

Hi,

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

HTH
Cheers
Carim


--
Carim
------------------------------------------------------------------------
Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
View this thread: http://www.excelforum.com/showthread...hreadid=529820

Hi,

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

HTH
Cheers
Carim


--
Carim
------------------------------------------------------------------------
Carim's Profile: http://www.excelforum.com/member.php...o&userid=33259
View this thread: http://www.excelforum.com/showthread...hreadid=529820