Finding the average of non-zeros from a filtered list

[Anglais428v2]

Hi,

I have a formula that works in that it gives me a filtered list of numbers. The formula is:
=FILTER(Jobs,ISNUMBER(MATCH(Type,$L$3,0))*ISNUMBER(MATCH(SS,$A$3:$A$10,0))*ISNUMBER(MATCH(Reg2,$A23,0)))

I now want to get the average of the list of these numbers but exclude zeros from the calculation. I've been trying to wrap the above formula into an averageifs or average(if formula but it doesn't seem to be working.

Any advise?

Thanks

 

[MagnarH]

Can't you filter the spill range again, and use average on that range?
just remember to use the CHOOSECOL funtion.

=AVERAGE(FILTER(CHOOSECOLS(K4#,6),CHOOSECOLS(K4#,6)<>0))

Or is it more complex than that?

 

[Fluff]

If your formula is in A2 & only has one column try

=average(filter(a2#,a2#<>0))

 

[Anglais428v2]

If your formula is in A2 & only has one column try

=average(filter(a2#,a2#<>0))

I had tried this and it works, however, I am looking at a solution that does it all in one cell (rather than two separate calculations). But when I add my first formula above to the average formula it doesn't work.

 

[Fluff]

Try

=AVERAGE(FILTER(Jobs,ISNUMBER(MATCH(Type,$L$3,0))*ISNUMBER(MATCH(SS,$A$3:$A$10,0))*ISNUMBER(MATCH(REG2,$A23,0))*(Jobs<>0)))