doing stats and not including hidden rows or columns

gallan

Board Regular
I have columns of data (marks on assessments) that filter in various ways. When I filter I simply hide the rows that do not match the filter key. Then I want do stats on the visible data, eg averages, Standard Deviations etc. However Excel seems to include the hidden rows when I this. Is there a way to get Excel to ignore hidden rows in its calculations?

I could just copy the data on the visible rows to another worksheet, do the stats there and then copy it back to the appropriate cells but if there is a built in method I would like to know.

Thanks

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Damon Ostrander

MrExcel MVP
Hi Gary,

Here is a way to make most Excel worksheet functions look at just the visible cells in a range. I make use of a user-defined function I call Vis(). As an example of its use, suppose you want to average the visible cells in the range B2:B200. Simply call the AVERAGE function like this:

=AVERAGE(Vis(B2:B200))

Here's the code for the Vis function.

Code:
``````Function Vis(Rin As Range) As Range
'Returns the subset of Rin that is visible
Dim Cell As Range
Application.Volatile
Set Vis = Nothing
For Each Cell In Rin
If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
If Vis Is Nothing Then
Set Vis = Cell
Else
Set Vis = Union(Vis, Cell)
End If
End If
Next Cell
End Function``````

This works with both hidden rows and hidden columns.

To install this function in your workbook, go to the Visual Basic Editor (keyboard Alt-TMV), insert a new macro module (Alt-IM), and paste this code into the Code pane.

tusharm

MrExcel MVP
Check out the SUBTOTAL function. It supports 11 stats functions and it operates on either all cells or on only visible cells depending on the first argument. For more see Excel help.

I have columns of data (marks on assessments) that filter in various ways. When I filter I simply hide the rows that do not match the filter key. Then I want do stats on the visible data, eg averages, Standard Deviations etc. However Excel seems to include the hidden rows when I this. Is there a way to get Excel to ignore hidden rows in its calculations?

I could just copy the data on the visible rows to another worksheet, do the stats there and then copy it back to the appropriate cells but if there is a built in method I would like to know.

Thanks

gallan

Board Regular
I appreciate both replies. I will try subtotal first
Thanks

Replies
3
Views
1K
Replies
2
Views
2K
Replies
0
Views
102
Replies
3
Views
515
Replies
4
Views
636

1,191,166
Messages
5,985,050
Members
439,935
Latest member
Monty238

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back