Average of cells in a column

D

Daniel

Hi
How can I write a function Average of cells in a column but not count hiden
cells?

Normally I write: Average(D: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
 
D

Daniel

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

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.
 
P

Peo Sjoblom

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

=SUBTOTAL(101,D:D)

will only average visible cells

in earlier versions you would need a VBA function


--


Regards,


Peo Sjoblom
 
P

Peo Sjoblom

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

Look up SUBTOTAL as a function in help

=SUBTOTAL(101,D:D)

will average

=SUBTOTAL(109,D:D)

will sum


102 is COUNT

103 is COUNTA

104 is MAX

105 is MIN


--


Regards,


Peo Sjoblom
 
D

Daniel

Thanks all, you saved my life
Daniel

Peo Sjoblom said:
You couldn't even try it before you posted back!!?

Look up SUBTOTAL as a function in help

=SUBTOTAL(101,D:D)

will average

=SUBTOTAL(109,D:D)

will sum


102 is COUNT

103 is COUNTA

104 is MAX

105 is MIN


--


Regards,


Peo Sjoblom
 
D

Don Guillett

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

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.

JLatham said:
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: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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top