How to get average of percentages in a column with filtered cells and excluding zero

[simonedeane1986]

Hello Brains Trust!
I have a spreadsheet where i'm calculating, among other things, percentages down a column. I need to filter the various other columns against differing criteria with simple drop down filters.
Most cells in this percentage column will have a number, but some are zero.
I have managed to average all cells in the column and exclude the zeros using the formula =AVERAGEIFS(N5:N250,N5:N250,"<>0")
However, i would also like to be able to calculate what the average percentage of this column is when the other columns are filtered, whilst excluding any zero values.
(I can give a filtered average using the SUBTOTAL(1) function, but this still includes the zero values)
I cannot seem to find a way to do this?
Does anyone know of a solution to this problem?
Thanks!

 

[shift-del]

Hi Simone

In this example rows 3 and 6 are filtered.
The formula in column B is in B2:12.

Mappe2
	A	B	C	D	E	F
1	Number	Filter			excl. 0	excl. 0 and filtered items
2	18	1			23,111111	24,571429
4	19	1
5	0	1
7	33	1
8	0	1
9	23	1
10	33	1
11	14	1
12	32	1
Tabelle1

Cell Formulas
Range	Formula
E2	E2	=AVERAGEIFS(A2:A12,A2:A12,"<>0")
F2	F2	=AVERAGEIFS(A2:A12,A2:A12,"<>0",B2:B12,1)
B2,B4:B5,B7:B12	B2	=SUBTOTAL(3,A2)

 

[Fluff]

Another option

=AVERAGE(FILTER(N2:N250,(N2:N250<>0)*(MAP(N2:N250,LAMBDA(m,SUBTOTAL(103,m))))))