How to hide #VALUE! in cells ????

Hi,

There are empty cells in a column.

Where the cells are empty, they are reading #VALUE! because I have copied down a formula in all other cells above and below etc.

Is there any quick way of making the #VALUE! disappear or by hiding?

(Other than the copied formulas in the column, there are also conditional formatting so when the numbers drop below zero value, it turns the numbers red).

It's a long sheet and I want to avoid deleting each #VALUE! by hand !

Thanks.

Hi,

You have two options.

If you haven't used all three Conditional formats (assuming XL 2003 or less) then you could format the font to be white if there's an error. Otherwise why not just wrap the formula in an IF(ISERROR(yourformula),"",yourformula)

HTH

I dont know which formula you are using by using -- operator we can suppress #value in many cases eg

=sumproduct(--(conditon) , --( condition2) , etc ))

Can you tell us the formula in the cell?

How about?

=IF(SUM(F2072,J2072),F2072-J2072,"")

Hi,

can u post a sample of ur workbook..

may be u are calculating a numeric field but there are some text data in them.. (in the form of white space in front of the number)

Take a look here

http://support.microsoft.com/kb/100825

I would suggest

=IF(COUNT(F2072,J2072)=2,F2072-J2072,"")

Calculation only undertaken if both values are numbers.

4 years old thread.....