Excel: How to stop '#div/0!' from appearing in empty cells.

[Guest]

Using Excel: When setting up a workbook that shows averages over 5 cells I
keep getting '#DIV/0!' error in the formula cell when no value has yet been
entered into the cells. I have tried the suggested fixes but this affects the
averaging of the cells. All I want is to have the formula cell remain blank
when no data has yet to be entered into the 5 feeder cells. OR "yes Excel, I
know it is an error at the moment but I don't want you to tell me!"

 

[T. Valko]

Try this:

Suppose your formula looks like this:

=AVERAGE(A1:A5)

Use this to "trap" any errors:

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

Biff

 

[Dave Peterson]

Another way is to check for numbers first:

=if(count(a1:a5)=0,"No numbers entered",average(a1:a5))

 

[Guest]

Thanks Dave.
Your solution worked a treat.
Cheers Gary