Ask Your Question
1

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

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

Cantagril gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

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

Opaque gravatar image

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

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.

edit flag offensive delete link more

Comments

2

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-07-12 13:49:34 +0200 )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 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 98 times

Last updated: Jul 12