Help removing DIV/0 errors when data isn't populated yet!

[Silvermax2k2]

Hey all!

So I have a very large excel book that pulls data from several different places. Since this book encompasses data for the entire year, a lot of my formulas are producing DIV/0 errors. These errors, while valid, are rather unsightly. I was reading that there is a way to have the cell be blank, but I am not entirely understanding how to do it. The two main formulas that are throwing DIV errors are:

=AVERAGE(C8:F8)

And

=l58
(I pull a lot of data from other pages - I have literally hundreds of DIV errors due to the =cell formula!)

Thanks so much for the help in advance!

 

[MisterBate5]

=if(counta(C8:F8)=0,"",AVERAGE(C8:F8))

 

[GK039]

Try

if(iserror(AVERAGE(C8:F8)),0,AVERAGE(C8:F8)) in 2003 or the iferror in 2007-2010

George

Hey all!

So I have a very large excel book that pulls data from several different places. Since this book encompasses data for the entire year, a lot of my formulas are producing DIV/0 errors. These errors, while valid, are rather unsightly. I was reading that there is a way to have the cell be blank, but I am not entirely understanding how to do it. The two main formulas that are throwing DIV errors are:

=AVERAGE(C8:F8)

And

=l58
(I pull a lot of data from other pages - I have literally hundreds of DIV errors due to the =cell formula!)

Thanks so much for the help in advance!

 

[Silvermax2k2]

That worked, but it is giving me a zero, which will throw off my averages. Do you know of a way to just make the cell blank, or for it to show "NA"? thanks again!!

Try

if(iserror(AVERAGE(C8:F8)),0,AVERAGE(C8:F8)) in 2003 or the iferror in 2007-2010

George

 

[GK039]

Replace 0 with double quotes ""

That worked, but it is giving me a zero, which will throw off my averages. Do you know of a way to just make the cell blank, or for it to show "NA"? thanks again!!

 

[GK039]

Replace 0 with double quotes ""

Meaning replace 0 in the formula i posted before with "" so it will be:

if(iserror(average(c8:f8),"",average(c8:f8))

 

[Aladin Akyurek]

Hey all!

So I have a very large excel book that pulls data from several different places. Since this book encompasses data for the entire year, a lot of my formulas are producing DIV/0 errors. These errors, while valid, are rather unsightly. I was reading that there is a way to have the cell be blank, but I am not entirely understanding how to do it. The two main formulas that are throwing DIV errors are:

=AVERAGE(C8:F8)

And

=l58
(I pull a lot of data from other pages - I have literally hundreds of DIV errors due to the =cell formula!)

Thanks so much for the help in advance!

Something like:

=IF(COUNT(C8:F8),AVERAGE(C8:F8),"")

=IF(ISNUMBER(I58),I58,"")

 

[Silvermax2k2]

AH!!! Perfect!!! Thank you very much all!!

 

[scottylad2]

Excel Workbook
	G
2	125
3	20
4	32
5	0
6	0
7	15
8	20
9	14
10	37.66667
Sheet1

Excel 2010

Cell Formulas
Range	Formula
G10	=SUM(G2:G9)/COUNTIF(G2:G9,">0")

 

[Silvermax2k2]

Ok, so one more question!

Is there a way I can get a "running average" for the year, even if all the data is not populated yet, and the cells are showing div/0? (They are no longer showing the div/o due to the help you gave, but I'm assuming they are just "hidden")

thanks again, you guys rock!