Ask Your Question

Calc:Conditional formatting-How to stop empty cell being treated as zero?

asked 2019-07-12 13:17:39 +0100

Cantagril gravatar image



I am using the numerical value of a cell in one column to change the background colour in the adjacent one using the conditional formatting tool.

I have 3 formulae which are working fine except when the source cell is empty. The "treat empty cells as zero" option is NOT turned on but the result of this formula returns the result as if it were zero. I have tried conditions using both a formula and a cell value to return either "" or a white background in the first and last positions in the condition ladder but no joy.

The formula is "IF((AND(P17>=0,P17<6)))" which works perfectly well for the value range in the source cells.

Any help would be very gratefully received:-)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-07-12 13:34:42 +0100

Opaque gravatar image

updated 2019-07-12 13:37:59 +0100


though it does not fully answer your question in the sense of stopping to treat empty cells as zero.

  1. IF is not required, since it is a Conditional Format
  2. Use AND(P17>=0,P17<6,ISNUMBER(P17))

Note: I got no 5.x version any more and thus there is some uncertainty regarding availability of function ISNUMBER in LO v5.x. I've checked my answer using LO

Hope that the helps.

edit flag offensive delete link more



ISNUMBER is available there; FYI: in help for 6.3, we have added information regarding when function were introduced - tdf#123803 (see e.g. REGEX) - so you may use, then in the resulting URL, change 6.2 to 6.3.

Mike Kaganski gravatar imageMike Kaganski ( 2019-07-12 13:49:34 +0100 )edit

Thank you so much!!

This has been making my life a misery for the last couple of days - now my spreadsheet looks just the way I wanted it:-)

Cantagril gravatar imageCantagril ( 2019-07-12 14:20:45 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-07-12 13:17:39 +0100

Seen: 435 times

Last updated: Jul 12 '19