Would a user defined function work for you? If so, copy this code into a
regular code module (press [Alt]+[F11] and then Insert | Module and copy and
paste the code into the code module presented to you)
Public Function AverageOfVisible() As Double
Const columnToEvaluate = "D" ' change as necessary
Dim lastRow As Long
Dim itemCount As Long
Dim itemTotal As Double
Dim rOffset As Long
Dim baseCell As Range
Application.Volatile
lastRow = Range(columnToEvaluate & _
Rows.Count).End(xlUp).Row
itemCount = 0
itemTotal = 0
Set baseCell = Range(columnToEvaluate & "1")
For rOffset = 1 To lastRow - 1 ' assumes row 1 is label
If baseCell.Offset(rOffset, 0).EntireRow.Hidden = False Then
If IsNumeric(baseCell.Offset(rOffset, 0)) Then
itemCount = itemCount + 1
itemTotal = itemTotal + baseCell.Offset(rOffset, 0).Value
End If
End If
Next
If itemCount > 0 Then
AverageOfVisible = itemTotal / itemCount
Else
AverageOfVisible = 0
End If
End Function
To use the function on a worksheet, just enter this formula:
=AverageOfVisible()
It could be adapted to work in several different columns by passing a column
indicator to the UDF. As written it works in D but that is changeable to any
other individual column.
Daniel said:
Hi
How can I write a function Average of cells in a column but not count hiden
cells?
Normally I write: Average(D
) but this function will calculate average all
cells even hiden cells
In my spread sheet I have some hiden cells such as below
Cell in row 1
2
3
4
100
101
102
....
i just want to calculate average of cell 1,2,3,4,100,101,102 but skip cells
in row 5 to 99
Thanks
Daniel
