# Do not show #Value!

Hello im having some trouble with office calc. As you can see bellow on the image i am getting as a result in some cells the #VALUE!, but i dont want it to be seen. Any idea how i could do this ? Also you can see the the equation for the cell. I tried to uncheck displaying zero values but no success. And finaly any idea how i could make the -##:##:## to be show in red ? i succeded to do it on the positive but on negatives had no success. Thanks in advance

edit retag close merge delete

Sort by » oldest newest most voted

Hello @kik94

You can nest your formula in IFERROR formula to display something different, than error code if some error occured. So it could look like IFERROR(Your_Formula;"") Regarding conditional formatting - your formula returns text string if value in L column is below 0. So if you are using Cell Value is below 0as your Conditional Formatting test, it returns false, cause text string can not be compared to numbers. Or use Formula is -> VALUE(L73)<0 (and of course adjust Cell reference to your actual range) or, which is definately better way, rework your formulas to get only numbers as formula result and do not mix text with numbers and then apply Conditional formatting rules to the values. If you can add sample file to your question, we will be glad to help you with such transformation.

more

Seeing the highlighted formula expression there you don't need that at all. In fact it worsens things. You try to force a wall clock time format to display a negative sign that yields a string result instead of numeric result. Doing so at other places may actually also be the cause for the #VALUE! error because -L73 tries a numeric operation on the value of L73 but if that is not numeric but text instead then it can fail with the #VALUE! error.

Instead, simply apply the time duration number format [HH]:MM:SS or to display negative times in red use [HH]:MM:SS;[RED]-[HH]:MM:SS

more