need help to get average from multiple worksheets ommiting (0)@blank in new sheet

[razlanadli]

Hello

I'm new to the forum and excel. I'm using excel 2007. I need help on getting an average from multiple sheets into a sheet.
I've named the multiple sheets as WA to WA(12). and i would like to get the average from these sheets to a sheet called LE15X in the column weekly assessment. i've succeded by using this formula =AVERAGE('WA:WA (12)'!I11). My problem is, how can i get the average by ommiting the value zero(0) or blank in any the WA to WA(12) sheets.

With this I attached the sample Data. Any help is very much appreciated. TQ.

[NBVC]

Create a list of all the sheets from WA to WA(12) in a separate area somewhere, and name that range, say SheetNames, by selecting the range and typing the name in the Name Box, just to the left of the Formula Bar.

Then use formula:

=IFERROR(SUMPRODUCT(SUMIF(INDIRECT("'"&SheetNames&"'!I"&ROW(I11)),">0"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&SheetNames&"'!I"&ROW(I11)),">0")),"")

copied down.