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

[Cantagril]

Version: 5.1.1.3

Context:

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:-)

[anon73440385]

Hello,

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 6.2.5.2

Hope that the helps.

[mikekaganski]

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 https://help.libreoffice.org, then in the resulting URL, change 6.2 to 6.3.

[Cantagril]

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:-)