Excel conditional formatting - Highlight numbers greater than 5 but ignore any formula

3
votes

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.

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.

Thanks in advance, James.

3
excelms-officeopenoffice.orgconditional-formatting
Which column are you trying to highlight? – Siddharth Rout
which cell are you trying to colour? C6, D6 or B6? and what criteria did you use in your conditional formatting? – Sam
My international settings are set to English-GB, the semi colons are there because I believe that is how Open-office handles formulas, also when the client opens it, it looks like excel converts the formula because it shows up with commas in excel. – JamesZeinzu
I am trying to colour the cells in colum E that contain the formula shown(the idea is to highlight the cell if the result of the D6-LEN(B6) is greater than 5(it is to highlight is the user has gone over a character limit). – JamesZeinzu
See the answer posted below. – Siddharth Rout

3 Answers

6
votes

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

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

Note: Replace , with ; in your formula

Screenshot

enter image description here

EDIT

TRIED AND TESTED IN Open Office 4.1.3.2

enter image description here

2
votes

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...

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)

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:

=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

0
votes

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)