Returns the average value from the visible, non blank cells.


* This function excludes hidden cells.
* You can use the AVERAGE function to include hidden cells.
* For instructions on how to add a function to a workbook refer to the page under Inserting Functions

Public Function AVERAGEVISIBLE( _ 
            ByVal rgeValues As Range) As Double

Dim rgeCell As Range
Dim itotalcells As Integer
Dim dbtotalvalue As Double
Dim dbaverage As Double
   For Each rgeCell In rgeValues
      If (rgeCell.EntireRow.Hidden = False) And _
         (rgeCell.EntireColumn.Hidden = False) Then
         dbtotalvalue = dbtotalvalue + rgeCell.Value
         itotalcells = itotalcells + 1
      End If
   Next rgeCell
   dbaverage = (dbtotalvalue / itotalcells)
   AVERAGEVISIBLE = dbaverage
End Function

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext