SUBTOTAL function to average column without blanks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I can use the SUBTOTAL command with the 101 function to average only visable
cells in a filtered list.
However how can I exclude empty cells from being included in the average?
I can do this with an "array formula", e.g.
{=AVERAGE(IF(P3:P60<>0, P3:P60,""))}
but not with a filtered list using visable cells only.
Any help appreciated.
 
I take it you mean a list where rows have been manually hidden, instead
of filtered. In any case, the SUBTOTAL function ignores empty cells.
Or do you want an average that excludes zeros. If so, try the following
formula, which needs to be confirmed with CONTROL+SHIFT+ENTER...

=AVERAGE(IF(SUBTOTAL(102,OFFSET(P3:P60,ROW(P3:P60)-ROW(P3),0,1)),IF(P3:P6
0 >0,P3:P60)))

Hope this helps!
 
Cheers Domenic,
Thank you so much - you solved my problem, although I'll have to work on it
a bit to fully understand why!
FYI, I didn't have manually hide rows, they were only hidden by the filter.
The filter selected items from one column, and the corresponding amounts that
I wished to average were in another column, which included some blank cells.
Keep up the excellent work!
 
Back
Top