How to hide #DIV/0! in cells waiting for input?

[Pheasant Plucker®]

Hi there,

I have some cells with a formula such as;

=AVERAGEA(C12:C16)
=ROUND(C17/25,4)
=SUM(C17:G17)/100
=H17+H19
etc. etc.

Until a number is input into another cell which is included in one of the
formulas above the annoying #DIV/0! appears.

As soon as a number is input into a 'donor cell' included in one of the
calculations above then the #DIV/0! changes into its expected format.

Is there a way to hide the annoying #DIV/0! that is dotted all over the
spreadsheet while the 'donor' cell is empty before its result is known?

The sheet looks very untidy and gives the impression it is full of errors
while the #DIV/0! is displayed.

 

[Nikki]

You can use conditional formatting to hide errors.
clcick on cell A1
go to formatting-->conditional formatting--> formula is type =iserror(a1)
change the font color to white or any color that is your background color.
click ok. copy the cell and specail paste format to all other cells. if a
cell give #N/A or #Div/0 you won't see it but you also lose the ability to
see errors.

Hope this helps.

Nikki

 

[pinmaster]

You can have your formulas check for errors then return blank or 0 when an
error is found.

i.e
=IF(ISERROR(A1/B1),0,A1/B1)
or
IF(ISERROR(A1/B1),"",A1/B1)

HTH
JG

 

[chip_pyp]

you can also do a simple if formula....somehting like thins

=if(xx="","",formula)
where xx is a cell that is blank until you insert a value and the formula is
the AVERAGE(C12:C16) or which ever formula you normally had in there

 

[Pheasant Plucker®]

Many thanks for the suggestions here guys.

I eventually decided to use the example below as I felt it was the simplest
way to achieve what I wanted to do.

Thanks again to all.

Kind regards,
-=pp=-