Conditional formatting thinks formula blank "" is a value?

Emerlin

Board Regular
Joined
Jan 8, 2007
Messages
105
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.
 
Last edited:

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

pella88

Board Regular
Joined
Aug 14, 2013
Messages
67
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

Board Regular
Joined
Jan 8, 2007
Messages
105
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

Board Regular
Joined
Aug 14, 2013
Messages
67
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,424
Office Version
365
Platform
Windows
Probably simpler is
=AND(B9>7,B9<>"")
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,687
Messages
5,445,952
Members
405,372
Latest member
Vithanalas

This Week's Hot Topics

Top