Averaging without Hidden Cells

Susie has a worksheet that has quite a bit of data in it. It is not unusual for her to hide columns within the worksheet. She needs a formula that will return the average of a range of cells, all in the same row, while ignoring any hidden cells. For instance, if Susie selects B7:G7, she would like an average of only the cells in that range that are visible (columns D and E, in this case, are hidden).

There is no intrinsic function in Excel to calculate such an average. If you were trying to find the average of a range of cells in a column, then you could use the SUBTOTAL function, in this manner:

=SUBTOTAL(101,A7:A12)

The SUBTOTAL function won't return an accurate value when the range provided is within a row, however. You could, if desired, use some helper cells to do the calculation. Just put the following into cell B8:

=(CELL("width",B7)>0)+0

Copy cell B8 to the range C8:G8. The result is that each cell in the range B8:G8 will contain either a 0 or a 1 based on whether the column is hidden or not. You could then use the following formula to determine the average:

=SUMIFS(B7:G7,B8:G8,">0")/SUM(B8:G8)

It calculates the average only for those columns in which the cells in B8:G8 contain a value of 1 (they aren't hidden).

Of course, you may not be able to use the helper cells and you might find it more beneficial to create a user-defined function to do calculate the average. The following works very quickly:

Function AverageVisible(rng As Range)
    Dim rCell As Range
    Dim iCount As Integer
    Dim dTtl As Double

    iCount = 0
    dTtl = 0
    For Each rCell In rng
        If rCell.ColumnWidth > 0 _
          And rCell.RowHeight > 0 _
          And Not IsEmpty(rCell) _
          And IsNumeric(rCell.Value) Then
              dTtl = dTtl + rCell
              iCount = iCount + 1
        End If
    Next
    If iCount > 0 Then
        AverageVisible = dTtl / iCount
    Else
        AverageVisible = 0
    End If
End Function

In order to use the function, simply put this formula in the cell you want to contain the average:

=AverageVisible(B7:G7)

The function checks every cell in the range (which means you could use it on rows, columns, or, indeed, any range at all) to make sure it is not hidden and not empty. If the cell contains a numeric value, then it is used to calculate the average. If the range you specify contains no values that can be averaged, then the function returns a value of 0.

The function automatically runs every time the worksheet is recalculated. If you change which columns are hidden, Excel doesn't automatically recalculate. Thus, you'll need to force recalculation after hiding or unhiding columns.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13262) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021.