Average cells based on data in adjacent cells.....Again

[dwright1020]

First, thanks to XOR LX for pointing me in the right direction on my last post.
I'm trying to average a column of cells "L" based on data in several other cells while excluding zeros and blank cells. XOR LX gave me the formula that will do the average, but it's not omitting zeros and blank cells. I've tried tweaking it myself, but I'm getting an #DIV/0! error, so I assume I don't have something right.

=AVERAGE(IF((Detail!$W$2:$W$4019>TIME(9,59,0))*($W$2:$W$4019<TIME(14,0,))*($Z$2:$Z$4019="Monday")*($Q$2:$Q$4019="Y"),$L$2:$L$4019)) Averages with zeros and blanks.

=AVERAGE(IF((Detail!$W$2:$W$4019>TIME(5,59,0))*($W$2:$W$4019<TIME(10,0,))*($Z$2:$Z$4019="Monday")*($Q$2:$Q$4019="Y")*($L$2:$L$4019>="0"),$L$2:$L$4019)) Gives me the error.

Thanks again in advance.

[Jonmo1]

Perhaps

=AVERAGEIFS($L$2:$L$4019,$L$2:$L$4019,"<>0",$W$2:$W$4019,">"&TIME(9,59,0),$W$2:$W$4019,"<"&TIME(14,0,),$Z$2:$Z$4019,"Monday",$Q$2:$Q$4019,"Y")

[martindwilson]

you have excel 2007 have you tried averageifs?

[Jonmo1]

or change "0" in your formula to just 0

The quotes makes it a TEXT String, not the number 0.

[dwright1020]

=AVERAGE(IF((Detail!$W$2:$W$4019>TIME(5,59,0))*($W$2:$W$4019<TIME(10,0,))*($Z$2:$Z$4019="Monday")*($Q$2:$Q$4019="Y")*($L$2:$L$4019>0),$L$2:$L$4019)) Works. Thanks for all the help.