adding accross sheets in a workbook

T

Todd

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
 
D

Domenic

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(INDIRECT("'"&D1:D5&"'!G51"
))))

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

Hope this helps!
 
T

Todd

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
 
P

Peo Sjoblom

Since you need a factual list of all the sheet names in question you can't
resolve this except running a macro that will populate a dynamic range with
the sheetnames and then apply Domenic's formula or just use a dynamic list
and add them manually when you add them to the workbook

http://www.contextures.com/xlNames01.html#Dynamic
 
Top