Prev: Creating emailing list from column of addresses
Next: vba or excel help
From: JudyK on 9 Apr 2010 12:20
I have data in worksheet A for each month, for each entity. Then I have a
summary page which shows just the averages for each entity for each month.
Then I need to create a master summary page which shows just the annual
average for each entity, just one line per entity.

My Summary page shows Jan-Dec in column A. I have a formula averaging the
numbers from worksheet A. Jan-Mar is done. But the rest of the year hasn't
happened yet, so April's formula results "#DIV/0!". I have already put in
all the formulas for the year to be done with it. But I don't want all these
"#DIV/0!" to appear on the spreadsheet, it makes it look bad. I want those
cells to appear blank until there are actual numbers to be averaged, so the
formula can show a real result, not some goofy looking err-type message that
junks up the spreadsheet.


From: Toothsome Papa on 9 Apr 2010 12:35
dividing by something that isn't there IS an error, and Excel shows it as
such, regardless of what you think about it.

You need to change all the formulas to check for 0. =IF(A1=0, "", B1/A1)
something like that.

"JudyK" wrote:

> I have data in worksheet A for each month, for each entity. Then I have a
> summary page which shows just the averages for each entity for each month.
> Then I need to create a master summary page which shows just the annual
> average for each entity, just one line per entity.
>
> My Summary page shows Jan-Dec in column A. I have a formula averaging the
> numbers from worksheet A. Jan-Mar is done. But the rest of the year hasn't
> happened yet, so April's formula results "#DIV/0!". I have already put in
> all the formulas for the year to be done with it. But I don't want all these
> "#DIV/0!" to appear on the spreadsheet, it makes it look bad. I want those
> cells to appear blank until there are actual numbers to be averaged, so the
> formula can show a real result, not some goofy looking err-type message that
> junks up the spreadsheet.
>
>
 | 
Pages: 1
Prev: Creating emailing list from column of addresses
Next: vba or excel help