"#value" error

[dawn836]

Hi all

How can i make a cell empty instead of returning #value

 

[joerd]

What is the formula in your cell, and why does it return a #value error?
If you make a cell empty, there is nothing in it, not even an error nor a formula.
You may use ISERROR(A1) to test for an error in cell A1 (for example). With an IF statement, you can then output something you like better than the Excel error. For example: =IF(ISERROR(A1),"There is an error in A1.",A1) will output the value of A1 if there is no error, or a message if there is an error.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[MintJulep]

=IF(ISERROR(A1),"There is an error in A1.",A1)

Is a circular reference, and quite possibly will result in the end of the universe.

However, you could do something like:

=IF(ISERROR(A1),"There is an error in A1.",2*A2)

where 2*A2 could be any valid formula or function.

If you want A1 to APPEAR to be blank if the formula results in an error, you could use:

=IF(ISERROR(A1),"",2*A2)

However, having stated all of that, I'm not sure if #VALUE is considered an error. There is ISNA(). I forget if there is ISVALUE().

 

[joerd]

I anticipated that formula to be in A2 or B1 or whereever, but not in A1, obviously. So check the cell or formula result before you display it. Another example:
In A1 you'd have a number.
In A2 you want to display 1/A1, but not have a problem with #DIV/0! errors or text input or whatever other error. Then, you'd use as a formula in A2: [tt]=IF(ISERROR(1/A1),"Sorry",1/A1)[/tt]
Not very elegant, but working.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.