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!

 

[GK039]

If your data begins in cell A1 and for 15 cells try this:

=AVERAGE(OFFSET(a1,0,0,COUNTA(a1:a15),1))

you can change the counta(a1:a15) to whatever range you like in order to incorporate all of your data

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!

 

[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!

Oh, never mind - I'm stupid! I fixed it.

 

[Aladin Akyurek]

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!

Not clear. The formulas I posted prevent #DIV/0! from appearing at all.

Current query:

Suppose you have A2:A100 consisting of figures, #DIV/0! and B2:B100 consisting of dates...

Control+shift+enter, not just enter:

=AVERAGE(IF(YEAR(B2:B100)=E2,IF(ISNUMBER(A2:A100),A2:A100)))

where E2 houses a year of interest like 2011.

Is this what you wanted to have?