average only visible cells?

[nshah]

is there a way to average only visible cells in a list? i am using an
advanced filter and wan to automate the averages of columns following
the filter. however, i run in to issues of hidden rows being counted
in the average when i try to create the formula. is there a function
for "visible cells". i know you can go to edit->go to-> visible cells
to copy the cells, but i dont want to copy the cells to another sheet
and then take the averages there. thoughts?

 

[Peo Sjoblom]

One possible way

=SUBTOTAL(1,A2:A100)

where A2:A100 is the total range will only average filtered/visible cells

 

[Jerry W. Lewis]

Depends on what the OP meant by "hidden rows". SUBTOTAL may ignore rows
not displayed in a filter, but it definately uses rows hidden by
Format|Row|Hide. For that problem, you can use my IsVisible function at

http://groups.google.com/groups?selm=3F6073FD.1000108@no_e-mail.com

Jerry

 

[Peo Sjoblom]

In this case the OP mentioned filter

" i am using an advanced filter"