Greetings, I have a large data table that has autofilters on the headings.
I also have a "Data Summary" Sheet in
which I would like to get a conditional average.
Here is my *working* array formula:
=AVERAGE(IF('Data Table'!$C$10:$C$65536=A2,'Data Table'!$BP$10:$BP$65536))
The problem is, it averages all cell
values (conditional upon the C column's cell being equal to A2 of course), whereas I only want to average the VISIBLE cells
that match the condition.
I have tried using a little VBA with:
Function Vis(Rin As Range) As Range
'Returns the subset of Rin that is visible
Dim Cell As Range
Set Vis = Nothing
For Each Cell In Rin
If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
If Vis Is Nothing Then
Set Vis = Cell
Set Vis = Union(Vis, Cell)
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and then trying the formula:
=AVERAGE(IF(Vis('Data Table'!$C$10:$C$65536)=A2,Vis('Data Table'!$BP$10:$BP$65536)))
but it does not work.
Any ideas?? Thanks in advance.