Getting a cell to display a zero if another cell is #VALUE!
Posted by Chris Jones on June 07, 2001 11:49 AM
Hey there,
I would appreciate any help here
I want a cell that returns a value of zero if another cell has the #VALUE! statement, or to return the same number the cell has.
For example
Cell A5 is 0.5, therefore cell B5 is 0.5
Cell A6 is #VALUE!, therefore cell B6 is 0
Thank you
Chris
Posted by Aladin Akyurek on June 07, 2001 11:55 AM
In B5 enter & copy down:
=IF(ISERROR(a5,0,a5)
This will catch any type of error known to Excel. If you're only interested in the #VALUE! errors, change the formula to:
=IF(A5="#VALUE!",0,a5)
Aladin
Posted by Eric on June 07, 2001 11:59 AM
=IF(A1"#VALUE!",A1,0)
It worked for me
Posted by Barrie Davidson on June 07, 2001 12:00 PM
=IF(ISLOGICAL(ERROR.TYPE(A5)=3),0,A5)
Posted by Mark W. on June 07, 2001 12:00 PM
=IF(ISLOGICAL(ERROR.TYPE(A5)=3),0,A5)
Posted by Mark W. on June 07, 2001 12:03 PM
=#VALUE!="#VALUE!" produces #VALUE!
Posted by Aladin Akyurek on June 07, 2001 12:26 PM
Yep. Meant: =IF(ISERROR(A5),0,A5)
Posted by Chris Jones on June 07, 2001 12:30 PM
Thank you very much Mark, it worked perfectly
Posted by Chris Jones on June 07, 2001 12:30 PM
Thank you very much Mark, it worked perfectly
