Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error result for link formula | Excel Worksheet Functions | |||
if i sort cell that has link to another page how to keep link | Excel Discussion (Misc queries) | |||
if i sort cell that has link to another page how to keep link | Excel Discussion (Misc queries) | |||
ERROR.TYPE formula problem | Excel Worksheet Functions | |||
set up a link that updates page one from all other page entries? | Excel Worksheet Functions |