Link page formula error problem
 

I have an excel containing 13 worksheets. That's 1 worksheet for each month,
then a summary worksheet that each month's total average link-pastes into.
So in the summary worksheet, each 12 months average is shown in separate
cells and below is an entire year summary cell where these 12 totals are
averaged.
The problem is the entire year summary average cell an error #DIV/0!
I think it's because the months that we have not reached this year do not
have a total data yet. These months average cells also show the #DIV/0!

How can I fix the Average formula to work with the cells that have data and
skip the other months so an average for the months with values is shown?

Example:
In the summary worksheet:
"200" is in cell A-1, this is a link paste from another worksheet "May"
average for May.
"DIV/0!" is in cell A-2, this is a link paste from another worksheet "June"
average for June that does not contain any counts yet.
"DIV/0!" is in cell A-3, this cell contains formula "=AVERAGE(A1,A2)

Thank-you for any help!

Link page formula error problem
 

change your formula to return a Zero Value if no data is in that month

lets say your average on the month of june is calulated by averaging cells
A1:a10

instead of =AVERAGE(A1,A10)

use something like =IF(SUM(A1:A10)<1,0,AVERAGE(AVERAGE(A1:A10))


either that or pre fill your non used months with 0 instead of leaving them
blank

"jimtmcdaniels" wrote:

I have an excel containing 13 worksheets. That's 1 worksheet for each month,
then a summary worksheet that each month's total average link-pastes into.
So in the summary worksheet, each 12 months average is shown in separate
cells and below is an entire year summary cell where these 12 totals are
averaged.
The problem is the entire year summary average cell an error #DIV/0!
I think it's because the months that we have not reached this year do not
have a total data yet. These months average cells also show the #DIV/0!

How can I fix the Average formula to work with the cells that have data and
skip the other months so an average for the months with values is shown?

Example:
In the summary worksheet:
"200" is in cell A-1, this is a link paste from another worksheet "May"
average for May.
"DIV/0!" is in cell A-2, this is a link paste from another worksheet "June"
average for June that does not contain any counts yet.
"DIV/0!" is in cell A-3, this cell contains formula "=AVERAGE(A1,A2)

Thank-you for any help!

Link page formula error problem
 

=AVERAGE(IF(NOT(ISERROR(A1:A2)),A1:A2))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"jimtmcdaniels" wrote in message
...
I have an excel containing 13 worksheets. That's 1 worksheet for each
month,
then a summary worksheet that each month's total average link-pastes into.
So in the summary worksheet, each 12 months average is shown in separate
cells and below is an entire year summary cell where these 12 totals are
averaged.
The problem is the entire year summary average cell an error #DIV/0!
I think it's because the months that we have not reached this year do not
have a total data yet. These months average cells also show the #DIV/0!

How can I fix the Average formula to work with the cells that have data
and
skip the other months so an average for the months with values is shown?

Example:
In the summary worksheet:
"200" is in cell A-1, this is a link paste from another worksheet "May"
average for May.
"DIV/0!" is in cell A-2, this is a link paste from another worksheet
"June"
average for June that does not contain any counts yet.
"DIV/0!" is in cell A-3, this cell contains formula "=AVERAGE(A1,A2)

Thank-you for any help!