Ask Your Question

Do not show #Value!

asked 2019-02-06 11:58:30 +0200

kik94 gravatar image

updated 2019-02-06 12:46:43 +0200

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

image description

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-02-06 12:41:59 +0200

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.

edit flag offensive delete link more

answered 2019-02-07 12:25:21 +0200

erAck gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-02-06 11:58:30 +0200

Seen: 60 times

Last updated: Feb 07