3

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.

Improve this question
9
  • Which column are you trying to highlight? Commented Nov 7, 2013 at 9:24
  • which cell are you trying to colour? C6, D6 or B6? and what criteria did you use in your conditional formatting? Commented Nov 7, 2013 at 9: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. Commented Nov 7, 2013 at 9: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). Commented Nov 7, 2013 at 9:30
  • See the answer posted below. Commented Nov 7, 2013 at 9:30

3 Answers 3

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

15 Comments

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...)
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:/
@SamWard: I had to :)
@K_B: True. But it takes very less time to update the formula in Cond Formatting
@Siddhardt: his problem is he creates in OO, then opens in MSO and the behaviour is different in MSO than in OO
|
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...

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

Improve this answer

Comments

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)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.