Suppress @VALUE! on a template.

[Sol]

I have created an "Invoice" template to allow me to bill my client for work
done on an hourly basis. The template calculated the total cost for each job
and the total of all costs. However, in the cells that contain the
calculated values appears #VALUE! until the hours and rates are entered. Is
there a way to suppress that legend? This is important because the template
will generally show more rows than is needed for most clients.

 

[Sean Timmons]

=IF(ISERROR(CALCULATION),"",CALCULATION)

should do it.

 

[Shane Devenshire]

Hi,

You have a number of options:

1. in 2003 - write an error handling formula
=IF(ISERROR(myformula),"",myformula)
=IF(ISERR(myformula),"",myformula)
=IF(ISNA(myformula),"",myformula) we often use this one with VLOOKUP

2. In 2007 - write any of the above or better yet use
=IFERROR(myformula,"")

3. Suppress the display of the error by using conditional formatting

4. Suppress the printing of the error by choosing File, Page Setup, Sheet
tab, and under Cell errors as choose blanks

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Gord Dibben]

Generally you check for empty or not numeric cells and return a blank cell
instead of the error.

Or the simpler =ISERROR(formula),"",(formula)

But note that the ISERROR will mask any error.

What formulas do you have that return the #VALUE! error?


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Forgot the IF and misplaced parens.

=IF(ISERROR(formula),"",formula)


Gord