Friday, September 26, 2014

AVERAGE() UDF excluding hidden SUM(), COUNT(), etc.

WARNING:  HIGH LEVEL OF OPINION about a certain software company

The Problem

This week's question from one of Alan's subscribers was how to get subtotaling working when hiding columns instead of rows. She wanted to use the Average sub-function.  Following is an edited version of my response to Alan, plus a lot more.

First Discovery

I hadn't used the SUBTOTAL() function in years, which made me think the answer was simply to use it. 

Then I re-learned that SUBTOTAL() only works on hidden rows, primarily used with filters.  This seems very short-sighted of Microsoft as I can think of reasons when columns might be hidden as people consider budgeting options, excluding certain days of the week for operations reporting, etc. This makes me wonder how many times we've structured our worksheets to fit the tools provided.

IOW, the subscriber doesn't have an issue, Microsoft does.

The Direct Answer

Here is an approach that provides an answer to the subscribers immediate question, i.e. how to do an average of only the visible columns.  
  • The function walks through the ranges in rngSource(), inspecting each cell to see if its column is Hidden.
  • UNION combines the cells that are not Hidden into rngVisible.
  • The UNION approach eliminates double counting as would happen if rngSource() included overlapping ranges
  • rngVisible is then used as input to WorksheetFunction.Average.
  • Using a Worksheet function instead of writing code for the  calculations takes advantage of the error checking and reporting.
Function AveUnhiddenColumns(ParamArray rngSource())
    Dim rngCell As Range
    Dim rngVisible As Range
    Dim dblCtr As Double

    For dblCtr = LBound(rngSource) To UBound(rngSource)
        For Each rngCell In rngSource(dblCtr).Cells

            If Not rngCell.Columns(1).Hidden Then
                If rngVisible Is Nothing Then
                    Set rngVisible = rngCell
                    Set rngVisible = Union(rngVisible, rngCell)
                End If
            End If
        Next rngCell
    Next dblCtr
    Next dblCtr
    AveUnhiddenColumns = WorksheetFunction.Average(rngVisible)
End Function

What's next?

This has run long so I've decided to break the rest into other postings.  Here they are in the intended order of reading.