Excel条件格式 - 突出显示大于5的数字但忽略任何公式

时间:2021-09-24 20:24:03

I am having a strange problem, I have a column with this formula in each row respectively:

我有一个奇怪的问题,我在每一行都有一个这个公式的列:

=IF(C6="";"";D6-LEN(B6))

It works great, if the cell next to it is empty, make it an empty cell, if not, show the calculated length. However, when I try to use conditional formatting to make it red when the length is greater than 5, it highlights the cells that are 'blank'(have a formula in but are displaying blank). Any ideas as to why this is happening would be greatly appreciated.

它工作得很好,如果它旁边的单元格为空,则将其设为空单元格,如果不是,则显示计算的长度。但是,当我尝试使用条件格式在长度大于5时使其变为红色时,它会突出显示“空白”的单元格(具有公式但显示为空白)。任何关于为什么会发生这种情况的想法将不胜感激。

PS. I am currently using Open Office 4.0.1 and it seems to work fine, but when I send the document to a client(Excel 2010) it highlights the empty cells. I have tried clearing the conditional formatting and using Excels built in conditional for-matter and it does the exact same thing.

PS。我目前正在使用Open Office 4.0.1,它似乎工作正常,但当我将文档发送到客户端(Excel 2010)时,它突出显示空单元格。我已经尝试清除条件格式并使用内置条件格式的Excel,它完全相同。

Thanks in advance, James.

先谢谢你,詹姆斯。

3 个解决方案

#1


6  

Use this formula for conditional formatting (Tested in MS-OFFICE and not Open Office)

使用此公式进行条件格式化(在MS-OFFICE中测试,而不是在Open Office中测试)

=AND(E1<>"",E1>5)

Note: Replace , with ; in your formula

注意:替换,用;在你的公式中

Screenshot

截图

Excel条件格式 - 突出显示大于5的数字但忽略任何公式

EDIT

编辑

TRIED AND TESTED IN Open Office 4.1.3.2

在Open Office 4.1.3.2中进行了测试和测试

Excel条件格式 - 突出显示大于5的数字但忽略任何公式

#2


2  

Apparently Excel regards the empty string from a formula as having a value larger than ANY number. Just try it with numbers like 100000000 or -100000000 or 0...

显然,Excel将公式中的空字符串视为具有大于任意数字的值。只需尝试使用100000000或-100000000或0等数字...

This in contrast to an empty cell (or a cell without any entry, not even =""). A really empty cell has value = 0 for this comparison (again I suggest you play around with it)

这与空单元格(或没有任何条目的单元格,甚至不是“”“)形成对比。对于这种比较,一个真正空的单元格的值= 0(再次我建议你玩它)

Also Excel has no other way to create an empty string as a result from a formula, so in your conditional formatting you will have to check for the empty string returned from the formula. Just as Siddharth suggests change your conditional formatting formula into:

此外,Excel没有其他方法可以根据公式创建空字符串,因此在条件格式设置中,您必须检查从公式返回的空字符串。就像Siddharth建议将条件格式公式更改为:

=AND(E1<>"";E1>5)

= AND(E1 <> “”; E1> 5)

Yes this is strange behavior by Excel. And a shame an empty string and an empty cell arent the same thing for this (and many other) purpose

是的,这是Excel的奇怪行为。耻辱一个空字符串和一个空单元格对于这个(以及许多其他目的)来说都是一样的

#3


0  

I've just run into a similar problem and came up with the following workaround =AND(ISNUMBER(E1), E1>5). Formulae have to be localised into the interface language of Excel. E.g. in German that becomes =UND(ISTZAHL(E1); E1>5)

我刚遇到类似的问题,并提出了以下解决方法= AND(ISNUMBER(E1),E1> 5)。公式必须本地化为Excel的界面语言。例如。在德语中变为= UND(ISTZAHL(E1); E1> 5)

#1


6  

Use this formula for conditional formatting (Tested in MS-OFFICE and not Open Office)

使用此公式进行条件格式化(在MS-OFFICE中测试,而不是在Open Office中测试)

=AND(E1<>"",E1>5)

Note: Replace , with ; in your formula

注意:替换,用;在你的公式中

Screenshot

截图

Excel条件格式 - 突出显示大于5的数字但忽略任何公式

EDIT

编辑

TRIED AND TESTED IN Open Office 4.1.3.2

在Open Office 4.1.3.2中进行了测试和测试

Excel条件格式 - 突出显示大于5的数字但忽略任何公式

#2


2  

Apparently Excel regards the empty string from a formula as having a value larger than ANY number. Just try it with numbers like 100000000 or -100000000 or 0...

显然,Excel将公式中的空字符串视为具有大于任意数字的值。只需尝试使用100000000或-100000000或0等数字...

This in contrast to an empty cell (or a cell without any entry, not even =""). A really empty cell has value = 0 for this comparison (again I suggest you play around with it)

这与空单元格(或没有任何条目的单元格,甚至不是“”“)形成对比。对于这种比较,一个真正空的单元格的值= 0(再次我建议你玩它)

Also Excel has no other way to create an empty string as a result from a formula, so in your conditional formatting you will have to check for the empty string returned from the formula. Just as Siddharth suggests change your conditional formatting formula into:

此外,Excel没有其他方法可以根据公式创建空字符串,因此在条件格式设置中,您必须检查从公式返回的空字符串。就像Siddharth建议将条件格式公式更改为:

=AND(E1<>"";E1>5)

= AND(E1 <> “”; E1> 5)

Yes this is strange behavior by Excel. And a shame an empty string and an empty cell arent the same thing for this (and many other) purpose

是的,这是Excel的奇怪行为。耻辱一个空字符串和一个空单元格对于这个(以及许多其他目的)来说都是一样的

#3


0  

I've just run into a similar problem and came up with the following workaround =AND(ISNUMBER(E1), E1>5). Formulae have to be localised into the interface language of Excel. E.g. in German that becomes =UND(ISTZAHL(E1); E1>5)

我刚遇到类似的问题,并提出了以下解决方法= AND(ISNUMBER(E1),E1> 5)。公式必须本地化为Excel的界面语言。例如。在德语中变为= UND(ISTZAHL(E1); E1> 5)