Wednesday, December 17, 2014

SUBTOTAL() no longer needed!


Four previous posts were a journey around Excel's SUBTOTAL() worksheet function
  • It started with a need for someone to calculate averages from a row, ignoring only data in hidden columns
  • A complement to SUBTOTAL() was provided.
  • A replacement for SUBTOTAL() and it's complement
  • A recommendation to overcome a shortcoming of the Excel object model

Extravagant Boasts or Will Sonnett time?

This post has a User Defined Function that 
  • Makes SUBTOTALx() functions, both the Excel Worksheet function and the User Defined Functions provided in this series, obsolete.
  • Can turn many worksheet functions that use arrays or ranges into one of those sub-functions of SUBTOTAL(), i.e. working while ignoring hidden data, without 
    • Extending the list of numbers to cover all applicable Worksheet functions.  It's tough enough to remember 1-11 and 101-111.  Those are in alphabetical order and any new worksheet Function from Microsoft would be given the next higher number...then there is overlap with the 101-111...Too much of a mess to try!
    • Forcing users to type in the name of a Worksheet function as the first argument.
    • Tested functions include: QUARTILE.EXC() & QUARTILE.INC(),  PERCENTILE.EXC() & PERCENTILE.INC(), 
  • It does not work
    • When the Worksheet function needs a contiguous range.
    • When using a LOOKUP() function or it's relatives, e.g. MATCH() and INDEX()
    • Conditional functions such as : COUNTIF(), COUNTIFS(),  SUMIF() and SUMIF()

The Function

As with the previous functions, this one walks through the ranges specified and includes them in the resulting range if the row and column are visible.  If the cell has already been included in the resulting range, the Union has no effect.

Function VisibleRange(ParamArray rngFull()) As Range
    Dim rngVisible As Range
    Dim rngCell As Range
    Dim dblI As Double
    For dblI = LBound(rngFull) To UBound(rngFull) ' Considers each range
        For Each rngCell In rngFull(dblI).Cells ' Considers each cell in the current range
            If Not (rngCell.Rows(1).Hidden Or rngCell.Columns(1).Hidden) Then
                If rngVisible Is Nothing Then
                    Set rngVisible = rngCell ' Establishes the selected range
                    Set rngVisible = Union(rngVisible, rngCell) 
                    ' Adds 2nd and subsequent cells to the output
                End If
            End If
        Next rngCell
    Next dblI
    Set VisibleRange = rngVisible ' Assigns the collected range to the Function
End Function

How to use it

The primary way to use VisibleRange() is to include it within other functions, e.g. =SUM(VisibleRange(B2:D24).  It is not limited to a single range.  Here are some variations that have been tested
  • With disjoint ranges, e.g. =SUM(VisibleRange(B2:D24,X2:AA24, M2:M24))
  • Without regard for the same number of rows or columns in the  disjoint ranges, e.g. =SUM(VisibleRange(B2:D24, T4:T5, E7:G7))
  • With overlapping ranges, =SUM(VisibleRange(B2:D24, T4:T5, D7:G12)), the cells that are identified in more than one of the ranges (D7:D12 in this case) are used only once
  • Without regard for the same number of rows or columns in the  disjoint ranges, e.g. =SUM(VisibleRange(B2:D24, T4:T5, E7:G7))
  • With Named ranges, e.g. =SUM(VisibleRange(DataSet1, DataSet2))
  • With Named ranges on multiple worksheet, e.g. =SUM(VisibleRange('Clyde Study'!DataPoints, 'Selma Research'!DataPoints))