Formula that displays #DIV/0!

[Roger]

I have formulas set up in a spreadsheet that average a
block of cells based upon monthly data. When I autofill
the formula, it gives me a #DIV/0! message in the rows
that currently do not contain values. How can I create a
formula that will ignore the blank cells and leave my
spreadsheet clean?

 

[Domenic]

Hi,

try,

=AVERAGE(IF(A1:A10<>"",A1:A10))

entered using CTRL+SHIFT+ENTER.

Hope this helps!

 

[JulieD]

Hi Roger

embed your formula in an IF statement e.g.
=IF(A5="","",your_current_formula)
where A5 is the cell address awaiting values
or
=IF(ISERROR(your_current_formula),"",your_current_formula)

Cheers
JulieD

 

[hgrove]

JulieD wrote...

embed your formula in an IF statement e.g.
=IF(A5="","",your_current_formula)
where A5 is the cell address awaiting values or
=IF(ISERROR(your_current_formula),"",your_current_formula)

...

ISERROR is too broad and inefficient in this case, and should almos
never be used to trap #DIV/0! errors. In general, the ideal #DIV/0
test involves

=IF(Denominator,Numerator/Denominator,"")

but in the case of pre-filling average formulas when the range
referenced by those formulas don't yet contain numbers the idea
#DIV/0! test becomes

=IF(COUNT(RangeRef),AVERAGE(RangeRef),"")

Both of these pass through any #DIV/0! or other errors that occur i
RangeRef, which is generally desirable as diagnostics

 

[Juan Sanchez]

Roger

=IF(ISERROR(AVERAGE(A1:E1)),"",AVERAGE(A1:E1))

This should keep it clean.

It is not allways a good idea to hide errors. some times
they are real...!

You can also Contidional Format and use on the "Formula
Is" this: = IsError(F8)=True and set the fore color the
same as the back color...

This way the cell does contains #DIV/0! in case the error
needs to propagate to further cells.

Cheers
Juan