Thanks, it is a start. I have been working at this. The problem is that the
number of sheets is not static AND the cells with data are not static. So,
an added sheet may have data in cell g51 and it may not. The average needs
to count only the cells g51 that have data greater than zero.

Its the last part that is throwing me into a spin.


Todd





"Domenic" wrote:

Try the following array formula that needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=AVERAGE(IF(N(INDIRECT("'"&D1:D5&"'!G51"))0,N(IND IRECT("'"&D1:D5&"'!G51"
))))

....where D1:D5 contain your sheet names. Adjust this accordingly.

Hope this helps!

In article ,
"Todd" wrote:

Hi,

I am adding accross sheets in a workbook with SUM('Start!:End!'!G51) and
need a way to calculate the average of the result. The number of sheets
change and not always will the cells have data. Sometimes they will be blank
and sometimes they contain zero's. I can't figure out how to count how many
of the sheets have numbers larger than zero to divide with.

something like
SUM('Start!:End!'!G51)/(COUNTA('Start!:End!'!g51)

except able to know how many cells are blank or contain zero's.

Any ideas?


Todd