"#value" error
"#value" error
(OP)
Hi all
How can i make a cell empty instead of returning #value
How can i make a cell empty instead of returning #value
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS Come Join Us!Are you an
Engineering professional? Join Eng-Tips Forums!
*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail. Posting GuidelinesJobs |
|
RE: "#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.
RE: "#value" error
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().
RE: "#value" error
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: =IF(ISERROR(1/A1),"Sorry",1/A1)
Not very elegant, but working.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.