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

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

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

截图

EDIT

编辑

TRIED AND TESTED IN Open Office 4.1.3.2

在Open Office 4.1.3.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

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）