How can I fix my #DIV/0 outcome?

[artful]

I am calculating averages but get a nasty #DIV/0 outcome when part of my array is ZERO.

my forumla is: {=AVERAGE(IF(LEFT($W$3:$W$459,4)=J$462,IF($F$3:$F$459=$H464,IF($J$3:$J$459>0,$J$3:$J$459))))}

Can anyone suggest a solution?

 

[barry houdini]

One way would be to use

=SUMPRODUCT(--(LEFT($W$3:$W$459,4)=J$462),--($F$3:$F$459=$H464),--($J$3:$J$459>0),$J$3:$J$459)/MAX(1,SUMPRODUCT(--(LEFT($W$3:$W$459,4)=J$462),--($F$3:$F$459=$H464),--($J$3:$J$459>0)))

 

[Aladin Akyurek]

Also...

{=LOOKUP(9.99999999999999E+307,CHOOSE{1,2},0,AVERAGE(IF(LEFT($W$3:$W$459,4)=J$462,IF($F$3:$F$459=$H464,IF($J$3:$J$459>0,$J$3:$J$459))))))}

 

[artful]

Thanks for your answers guys. really appreciate it.

I was able to use Barry's answer
but I couldn't get Aladin's to work (came up with a error in formula 'CHOOSE').

 

[Aladin Akyurek]

Thanks for your answers guys. really appreciate it.

I was able to use Barry's answer
but I couldn't get Aladin's to work (came up with a error in formula 'CHOOSE').

Probably because of a missing paren...

{=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,AVERAGE(IF(LEFT($W$3:$W$459,4)=J$462,IF($F$3:$F$459=$H464,IF($J$3:$J$459>0,$J$3:$J$459)))))}

 

[artful]

Thanks Aladin, that did the trick.

bedankt