I'm trying to check if a cell is:
我正在尝试检查一个单元格是否:
- blank, and if false (not blank)
-
If value equals 0. I have tried:
如果值等于0.我试过:
=IF( NOT( ISBLANK($D:$D) ) & $D:$D=0 ,TRUE,FALSE)
= IF(NOT(ISBLANK($ D:$ D))&$ D:$ D = 0,TRUE,FALSE)
=IF( AND( NOT( ISBLANK($D:$D) ),$D:$D=0) ,TRUE,FALSE)
= IF(AND(不(ISBLANK($ D:$ D)),$ D:$ D = 0),TRUE,FALSE)
空白,如果为假(不是空白)
I am use Excel 2013 with Conditional Formatting rule: Use a formula to determine which cells to format > Format values where this formula is true
我使用带有条件格式规则的Excel 2013:使用公式确定要格式化的单元格>格式化此公式为true的值
What am I doing wrong? The problem is, the IF($D:$D=0) recognises cells with value 0 and those which are blank as true. I only want it to recognise 0 values. Not blank.
我究竟做错了什么?问题是,IF($ D:$ D = 0)识别值为0的单元格和空白为true的单元格。我只希望它识别0值。不是空白。
5 个解决方案
#1
7
I think this is what you're looking for:
我想这就是你要找的东西:
=AND(COUNTBLANK($D1)=0,$D1=0)
Note that when you apply it to the whole column, each cell will only look at its respective row. You'll want to keep the reference of $D1 instead of using $D:$D
请注意,将其应用于整个列时,每个单元格只会查看其各自的行。你想要保留$ D1的引用,而不是使用$ D:$ D.
#2
4
You can also use this formula
您也可以使用此公式
=($D1<>"")*($D1=0)
#3
0
The following will work and you don't have to worry about how AND and NOT fit together.
以下内容将起作用,您不必担心AND和NOT如何组合在一起。
=IF(ISBLANK(A1),TRUE,IF(A1=0,TRUE,FALSE))
#4
0
format only cells that contains -> (Specific text)... Don't use (cell value)--> then type "0" with no quotation marks
格式化仅包含 - >(特定文本)的单元格...不使用(单元格值) - >然后键入“0”,不带引号
this will format only cells with 0 inside it, blank cells will not be formatted
这将只格式化其中包含0的单元格,不会格式化空白单元格
#5
0
Before the rule specifying "0" you should add the rule "cell contains empty (blank) value". Then set the format to "no formatting" and then select "stop if true".
在规则指定“0”之前,您应该添加规则“单元格包含空(空白)值”。然后将格式设置为“无格式化”,然后选择“如果为真则停止”。
#1
7
I think this is what you're looking for:
我想这就是你要找的东西:
=AND(COUNTBLANK($D1)=0,$D1=0)
Note that when you apply it to the whole column, each cell will only look at its respective row. You'll want to keep the reference of $D1 instead of using $D:$D
请注意,将其应用于整个列时,每个单元格只会查看其各自的行。你想要保留$ D1的引用,而不是使用$ D:$ D.
#2
4
You can also use this formula
您也可以使用此公式
=($D1<>"")*($D1=0)
#3
0
The following will work and you don't have to worry about how AND and NOT fit together.
以下内容将起作用,您不必担心AND和NOT如何组合在一起。
=IF(ISBLANK(A1),TRUE,IF(A1=0,TRUE,FALSE))
#4
0
format only cells that contains -> (Specific text)... Don't use (cell value)--> then type "0" with no quotation marks
格式化仅包含 - >(特定文本)的单元格...不使用(单元格值) - >然后键入“0”,不带引号
this will format only cells with 0 inside it, blank cells will not be formatted
这将只格式化其中包含0的单元格,不会格式化空白单元格
#5
0
Before the rule specifying "0" you should add the rule "cell contains empty (blank) value". Then set the format to "no formatting" and then select "stop if true".
在规则指定“0”之前,您应该添加规则“单元格包含空(空白)值”。然后将格式设置为“无格式化”,然后选择“如果为真则停止”。