Conditional formatting thinks formula blank "" is a value?

[Emerlin]

Hi, I have a formula where I am checking if a cell is blank = if(a1="","",do math)

I am then applying conditional formatting to cells above a number. Any of the cells that are true or "", get applied that conditional formatting even thought it is blank.

Any ideas? I do not want "0" to show in all these cells.

Thanks.

 

[pella88]

Hi Emerlin,

The issue happens because your formula evaluates as "" in case of false statement, and this is considered as string. During conditional formatting, string is evaluated as higher than number value which returns positive on your conditional formatting.

In order to solve it, the easiest way is to use conditional formatting based on formula, and use following formula (do this for the first cell in your record, and then copy>paste format for all others)

=AND(B9>7,COUNTBLANK(B9)=0)

where B9 is cell with your formula, and 7 is the number which is your reference for conditional formatting. Formula works in way that your cell has to fulfil two criteria: the number has to be greater than defined number AND it cannot be blank > COUNTBLANK returns 1 in case that the cell is blank, including "").

Br
pella88

 

[Emerlin]

Hi Emerlin,

The issue happens because your formula evaluates as "" in case of false statement, and this is considered as string. During conditional formatting, string is evaluated as higher than number value which returns positive on your conditional formatting.

In order to solve it, the easiest way is to use conditional formatting based on formula, and use following formula (do this for the first cell in your record, and then copy>paste format for all others)

=AND(B9>7,COUNTBLANK(B9)=0)

where B9 is cell with your formula, and 7 is the number which is your reference for conditional formatting. Formula works in way that your cell has to fulfil two criteria: the number has to be greater than defined number AND it cannot be blank > COUNTBLANK returns 1 in case that the cell is blank, including "").

Br
pella88

Thank you so much - that was perfect. Your explanation was great!

 

[pella88]

Hi Emerlin,

Just to add one more solution (depending on what you try to achieve, it might also be useful).

In case that you put 0 as result for true statement, but you don't want to see it, you can always set a custom number not to show the resulting zero, for example:0;-0;;@

This will show numbers, but will set empty for 0. In this case, your original conditional formatting will work properly as the result of the condition of formula returns 0 (which is also a number), and the formatting will enable you to hide the 0.

Br
pella88

 

[Peter_SSs]

Probably simpler is
=AND(B9>7,B9<>"")

 

[Emerlin]

Thank you to both - these were both great solutions.

 

[Peter_SSs]

Thank you to both - these were both great solutions.

Glad you got a successful outcome.