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

Question

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.

which cell are you trying to colour? C6, D6 or B6? and what criteria did you use in your conditional formatting? — Sam, Nov 07 '13 at 09:25
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, Nov 07 '13 at 09:27
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, Nov 07 '13 at 09:30
If you insert a column of formulas next to your column E with `=E6>=5` you will see that Excel regards the empty string as being larger than 5. This will be true for the conditional formatting evaluation as well... — K_B, Nov 07 '13 at 09:36
And it also shows true in OO for the empty string(I find this very confusing, an empty string is most certainly not great than 5), is there any way to get around this? — JamesZeinzu, Nov 07 '13 at 09:39
You are not the first (and certainly not the last) to have problems with the poor exchangability of empty strings and empty cells: http://stackoverflow.com/questions/1119614/return-empty-cell-from-formula-in-excel — K_B, Nov 07 '13 at 09:57

Siddharth Rout · Accepted Answer · 2013-11-07T10:08:30.843

6

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

edited Nov 07 '13 at 10:08

answered Nov 07 '13 at 09:30

Siddharth Rout

147,039
17
206
250

I am not sure if the purpose is to duplicate the logic of the formula of column E into the conditional formatting (double maintenance when that formula changes...) – K_B Nov 07 '13 at 09:32
The problem is, I am trying to do this in Open-Office then send it to a person using Excel, and as far an I can tell, Open-Office doesnt let you use a formula for conditional formatting:/ – JamesZeinzu Nov 07 '13 at 09:33
@SamWard: I had to :) – Siddharth Rout Nov 07 '13 at 09:33
@K_B: True. But it takes very less time to update the formula in Cond Formatting – Siddharth Rout Nov 07 '13 at 09:34
@user2879468: Sorry. I don't have OO. how are you currently using it in OO? – Siddharth Rout Nov 07 '13 at 09:35
It is just a simple inbuilt open-office cell value is great than 5. – JamesZeinzu Nov 07 '13 at 09:37
1

@Siddhardt: his problem is he creates in OO, then opens in MSO and the behaviour is different in MSO than in OO – K_B Nov 07 '13 at 09:37
@k_B: I was editing my above comment when you posted. In MS office instead of the formula that i suggested, one can also check if e6="" and e6>5 then we do not need that formula. Regarding OO, I have some questions for OP. – Siddharth Rout Nov 07 '13 at 09:39
@user2879468: Do you have an option where you can compare if cell e6<>"" and e6>5 ? – Siddharth Rout Nov 07 '13 at 09:39
@SiddharthRout agreed! – K_B Nov 07 '13 at 09:40
The problem is I am doing what you just suggested in OO and it works great... but my client is using MSO and the conditional formatting works different in excel. – JamesZeinzu Nov 07 '13 at 09:41
@user2879468: I think you mixed your words in the last comment? It's the reverse right? – Siddharth Rout Nov 07 '13 at 09:42
Changed my sheet to using the formula above, sent it to the client, it works great! thank you very much for all of your help :) – JamesZeinzu Nov 07 '13 at 09:58
@user2879468: Now you tell me :D I already downloaded OO... LOL – Siddharth Rout Nov 07 '13 at 09:59
@user2879468: Anyways, since I had already downloaded OO, I have updated my answer. – Siddharth Rout Nov 07 '13 at 10:10

K_B · Answer 2 · 2013-11-07T09:48:02.687

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

score 0 · Answer 3 · answered Jan 05 '16 at 18:36

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)

answered Jan 05 '16 at 18:36

Charlie Clark

18,477
4
49
55

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

3 Answers3