adding accross sheets in a workbook
 

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

adding accross sheets in a workbook
 

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

adding accross sheets in a workbook
 

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

adding accross sheets in a workbook
 

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


--

Regards,

Peo Sjoblom

"Todd" wrote in message
...
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