Bug 68441 - FORMATTING: conditional custom cell formatting expressions fail with @ token
Summary: FORMATTING: conditional custom cell formatting expressions fail with @ token
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
3.5 all versions
Hardware: Other All
: medium normal
Assignee: Not Assigned
Whiteboard: BSA
Depends on:
Reported: 2013-08-22 16:31 UTC by Alexis Bezverkhyy
Modified: 2015-03-03 12:45 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

spreadsheet demonstrating the bug (9.35 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-08-22 16:31 UTC, Alexis Bezverkhyy
xls file demonstrating the bug (6.00 KB, application/vnd.ms-excel)
2014-05-05 16:54 UTC, Alexis Bezverkhyy
screenshot of xls rendered in LS (23.25 KB, image/png)
2014-05-05 16:54 UTC, Alexis Bezverkhyy
screenshot of xls rendered in MS Office (3.72 KB, image/png)
2014-05-05 16:55 UTC, Alexis Bezverkhyy
printscreen (22.68 KB, image/png)
2015-02-23 21:12 UTC, raal

Note You need to log in before you can comment on or make changes to this bug.
Description Alexis Bezverkhyy 2013-08-22 16:31:26 UTC
Created attachment 84465 [details]
spreadsheet demonstrating the bug

Problem description: 
Consider the following cell format : [<0.05][BLUE]"small";[<0]"negative";@
it should print "small" for numbers between 0 and 0.05, "negative" for negative numbers and treat all other data as text.

Steps to reproduce:
Try entering a number like 42 into a cell formatted with the expression above.

Current behavior:
When you enter a number like 42, the cell remains empty.

Expected behavior:
The cell should print 42 formatted as text. This is the way it works on MS Excel.
Operating System: All
Version: 3.5 all versions
Comment 1 Joel Madero 2014-05-05 05:27:06 UTC
It's because the equation is wrong. The way you have it you could have two values (let's say you put in -10, it's both less than 0.05, and less than 0). This is an incorrect formula. Please go to the user list or ask.libreoffice.org to ask for help in how to correctly format the formula.

Closing as NOTABUG
Comment 2 Alexis Bezverkhyy 2014-05-05 09:02:03 UTC
There are real life XLS documents that don't render properly in LibreOffice because of this. Have you at least looked at the attached document ? If you still think this is not a bug, please post a link to the help page which describes this syntax in LibreOffice.
Comment 3 Joel Madero 2014-05-05 15:31:32 UTC
Yes I have and it's because your formula is wrong - please ask the user list for help as this isn't the appropriate place to get help on these kinds of things. You can't have a formula that has two true statements (because again, if you put in a value less than 0, two different statements would be true). 

... nevermind I just did it so here is the formula:


If you need an explanation of why you can ask the user list as I'm not exactly sure ;) I just played around with it and checked out this wiki: 

Comment 4 Alexis Bezverkhyy 2014-05-05 15:51:04 UTC
Please stop redirecting me to the user mailing list. I am not a user asking for help, I am not writing a spreadsheet wondering how to write a formatting expression. I'm a tech guy trying to understand why some XLS documents don't render in the same way in LibreOffice as in Microsoft Office.

The documentation doesn't say anything precise about those brackets expressions :


"You can use any combination of numbers and the <, <=, >, >=, = and <> operators."

It does never say that it's forbidden to have overlapping conditions nor does it say what happens in this case. And that's the problem I'm reporting in this ticket, this behavior should be documented and preferably be the same as in MS Office in order not to break documents which in not the case now.
Comment 5 Joel Madero 2014-05-05 15:55:06 UTC
The documentation is a wiki - feel free to update it to reflect what is needed (it's literally written by users/contributors).

But now you're providing additional info that was not in your original report. Your saying that the same formula works in Excel but not in LibreOffice . . . please attach a xls file that demonstrates this (you only attached an ods file which I presumed meant you were only working with LibreOffice as Microsoft has yet to fully support open document standards).

I'm moving this back to UNCONFIRMED just because I'm curious ;)

P.S. Please don't get offended that I directed you to user list, we deal with literally thousands of users and tens of thousands of bug reports - we can't be answering user questions and I had no indication that you were anything but a user asking a user question. 99 out of a 100 that presumption is correct and saves EVERYONE a lot of time
Comment 6 Alexis Bezverkhyy 2014-05-05 16:54:18 UTC
Created attachment 98502 [details]
xls file demonstrating the bug
Comment 7 Alexis Bezverkhyy 2014-05-05 16:54:45 UTC
Created attachment 98503 [details]
screenshot of xls rendered in LS
Comment 8 Alexis Bezverkhyy 2014-05-05 16:55:01 UTC
Created attachment 98504 [details]
screenshot of xls rendered in MS Office
Comment 9 Alexis Bezverkhyy 2014-05-05 16:58:00 UTC
I attached the XLS file and screenshots of it rendered in Libre Office and MS Office.

(No offense taken, but the first message was already saying that the result observed was different from MS Excel.)
Comment 10 Owen Genat (retired) 2014-07-28 10:25:15 UTC
(In reply to comment #0)
> Consider the following cell format : [<0.05][BLUE]"small";[<0]"negative";@
> it should print "small" for numbers between 0 and 0.05, "negative" for
> negative numbers and treat all other data as text.

I am not convinced that "treat all other data as text" is an accurate representation. Formats should be respected.

LO Calc Number Format Codes page:


> To include text in a number format that is applied to a cell that might 
> contain text, enclose the text by double quotation marks (" "), and then 
> add an at sign (@). For example, enter "Total for "@ to display "Total 
> for December".

MS Excel Number Format Codes page:


> If included, a text section is always the last section in the number format. 
> Include an at sign (@) in the section where you want to display any text 
> entered in the cell. If the @ character is omitted from the text section, 
> text you enter will not be displayed. If you want to always display specific 
> text characters with the entered text, enclose the additional text in double 
> quotation marks (" "). For example, "gross receipts for "@

Sounds to me like @ should only be displaying content under either Calc or Excel if it is text. For numbers (e.g., the 42 in the example) either # or 0 would appear to be required.

This is going to need a developer to determine.
Comment 11 raal 2015-02-23 21:12:43 UTC
Created attachment 113630 [details]

Look at the printscreen from excel 2010- cell format is recognized differently from excel2003. I propose to close bug as invalid.
Comment 12 Buovjaga 2015-03-03 12:45:55 UTC
I asked Eike Rathke about this and he said this can indeed be closed as invalid, so I'm doing it.