Average of cells in a column

[Daniel]

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

 

[Daniel]

what I meant is average of visible cells only.
Average(D) will caculate all cells
Daniel

 

[JLatham]

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.

 

[Peo Sjoblom]

What version of Excel are you using? If 2003 and later you can use

=SUBTOTAL(101,D)

will only average visible cells

in earlier versions you would need a VBA function


--


Regards,


Peo Sjoblom

 

[Daniel]

Hi Peo
Is SUBTOTAL calculate the sum ? but I need to calculate average
Thnks
Daniel

 

[Peo Sjoblom]

You couldn't even try it before you posted back!!?

Look up SUBTOTAL as a function in help

=SUBTOTAL(101,D)

will average

=SUBTOTAL(109,D)

will sum


102 is COUNT

103 is COUNTA

104 is MAX

105 is MIN


--


Regards,


Peo Sjoblom

 

[Daniel]

Thanks all, you saved my life
Daniel

You couldn't even try it before you posted back!!?

Look up SUBTOTAL as a function in help

=SUBTOTAL(101,D)

will average

=SUBTOTAL(109,D)

will sum


102 is COUNT

103 is COUNTA

104 is MAX

105 is MIN


--


Regards,


Peo Sjoblom

 

[Don Guillett]

Perhaps you should look in the help index for SUBTOTAL. Or, try the formula
presented

 

[JLatham]

Based on your response to other posts, I presume you're using Excel 2003 or
2007, so you can use the SUBTOTAL() offered by them. If it turns out you're
using something earlier, then the UDF I offered should do it for you.

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.

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

 

[Gord Dibben]

If the cells are hidden manually or by filtering and you have XL 2003 you can
use the SUBTOTAL function.

=SUBTOTAL(101,A:A)

If you have XL 2002 or earlier and rows are hidden by filter use this one

=SUBTOTAL(1,A:A)


Gord Dibben MS Excel MVP