doing stats and not including hidden rows or columns

gallan

Board Regular
Joined
May 27, 2007
Messages
79
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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top