Download it now!
Bug 91070 - opposite result in conditional formatting in LibO Calc
Summary: opposite result in conditional formatting in LibO Calc
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.3.7.2 release
Hardware: All Windows (All)
: medium major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Conditional-Formatting
  Show dependency treegraph
 
Reported: 2015-05-04 08:52 UTC by gmarco
Modified: 2017-08-05 13:52 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Jpg snipped screen (86.25 KB, image/jpeg)
2015-05-04 08:52 UTC, gmarco
Details
snipped before saving the file (99.35 KB, image/jpeg)
2015-05-04 20:41 UTC, gmarco
Details
snipped after reopening the file (114.31 KB, image/jpeg)
2015-05-04 20:42 UTC, gmarco
Details
test file xlsx (10.21 KB, application/vnd.openxmlformats-officedocument.spreadsheetml)
2015-05-07 09:19 UTC, raal
Details
new test 1 (47.62 KB, image/jpeg)
2015-05-07 17:54 UTC, gmarco
Details
new test 2a (81.64 KB, image/jpeg)
2015-05-07 17:55 UTC, gmarco
Details
new test 2b (see comments) (93.34 KB, image/jpeg)
2015-05-07 17:56 UTC, gmarco
Details
same bin on 1drive (see previous comment) (16.00 KB, application/octet-stream)
2015-05-11 21:07 UTC, gmarco
Details
screenshot of the help (47.42 KB, image/jpeg)
2016-09-21 20:31 UTC, gmarco
Details
xls test in 5.3.3.2 (203.72 KB, image/jpeg)
2017-05-15 07:56 UTC, gmarco
Details
ods test in 5.3.3.2 (205.47 KB, image/jpeg)
2017-05-15 07:57 UTC, gmarco
Details
Modifyed sample file (6.64 KB, application/zip)
2017-08-04 14:48 UTC, m.a.riosv
Details

Note You need to log in before you can comment on or make changes to this bug.
Description gmarco 2015-05-04 08:52:10 UTC
Created attachment 115301 [details]
Jpg snipped screen

Using CALC, by applying a conditioned format the result is the opposite of what is required (see the attachment: the condition is "red background" if cell value is greater than 12, the cell F65 contains the value -15, but the background is red). To get what wished I have to use the condition "less than."
Comment 1 raal 2015-05-04 11:08:46 UTC Comment hidden (obsolete)
Comment 2 Julien Nabet 2015-05-04 18:52:51 UTC
gmarco: also, could you give a try to last LO version?
Indeed, even if there's a bug in 4.3.7, since 4.3 branch is EOL (there won't be official 4.3.8), there won't be any fix on this branch.
Comment 3 gmarco 2015-05-04 20:41:06 UTC
Created attachment 115323 [details]
snipped before saving the file

The sample.xls (go to http://1drv.ms/1GWdqVq) shows what I am getting.
(NOTE: I used the link as here I cannot attach the xls file)
But there is something really unusual: before saving the file, I see only one style ("sfondo rosso"), that is applied to the cells; after saving, re-opening the file, there arebefore saving the file, I see only one style ("red background"), that is applied to the cells; after saving, re-opening the file, there are other 6 styles applied to each conditioned cell.
Here I attach the first snipped jpg (before_saving).
After this I'll attach the second jpg (after_reopening).
Comment 4 gmarco 2015-05-04 20:42:32 UTC
Created attachment 115324 [details]
snipped after reopening the file
Comment 5 gmarco 2015-05-04 20:51:16 UTC
Errata corrige:
But there is something really unusual: before saving the file, I see only one style ("sfondo rosso"), that is applied to the cells;

after saving, re-opening the file, there are other 6 styles applied to each conditioned cell.
Comment 6 Julien Nabet 2015-05-04 21:12:47 UTC
gmarco: thank you for your feedback.
For the test, could you give a try to LO 4.4.2?
(please rename your LO directory profile, see https://wiki.documentfoundation.org/UserProfile#Windows, before testing).

Obviously, the goal is to know if it can be reproduce with last LO version and a clean LO profile.
Comment 7 gmarco 2015-05-05 17:29:33 UTC
Hi Julien, I have not yet tried LO 4.4.2 as I recently (may 02) updated to 4.3.7_Win_x86 alike I was requested opening LO, but if this is really important I'll do: let me know.
I resetted instead my LO user profile renaming the previous (Windows8_OS\Users\GM\AppData\Roaming\LibreOffice\4\user renamed userold, then, opening SAMPLE.xls nothing has changed.
I confirm all that I said on previous append and also please note that after reopening, cells B8, B14, C20 has a different "character style" (look at "after_reopening" attachment).
I tried too:
saving the original xls as ods, reopening the ods nothing has changed, styles also (from formato-stili e formattazione) are the same without any new added (remember my previous append "after saving, re-opening the file, there are other 6 styles applied to each conditioned cell" as you can see in "after reopening" image).
Comment 8 raal 2015-05-07 09:19:50 UTC
Created attachment 115400 [details]
test file xlsx
Comment 9 raal 2015-05-07 09:31:58 UTC
I downloaded your file (it's xlsx). With LO 4.4.2 I see all styles. After save as xlsx and reopen, the file look the same as before. Please try with LO 4.4 and let us know if you still observe the problem.
Comment 10 gmarco 2015-05-07 17:54:26 UTC
Created attachment 115430 [details]
new test 1
Comment 11 gmarco 2015-05-07 17:55:38 UTC
Created attachment 115431 [details]
new test 2a
Comment 12 gmarco 2015-05-07 17:56:58 UTC
Created attachment 115432 [details]
new test 2b (see comments)

Hi Julien and Raal
I'll try to update 442 asap but actually look at this.
Let we try to fly over the initial case that occurs on a spreadsheet migrated from MS Excel, it still is so, it is difficult to reproduce, but I cannot find a way to correct it.
Trying and trying again i believe I have identified some states of apparent anomaly, easily reproducible (I can reproduce them at infinity), on which you should be able to investigate.
Explaining fully in english (which is not my native language) is for me a little complicated but I'll try to.

1) Create a new sheet and enter the values and formulas (A1 =230, A2 =225, A3 =A1-A2), copy col.A into col.B, creates a style and apply the conditional formatting ("Format-Conditional Formatting") on A3 (if = 5), do the same thing with B3;
now you can try and try to change the conditions in B3 and A3 and the result will be always correct.
1a) creates a new sheet as above, applies the conditional formatting on A3 and then copy it on B3 using "Copy - Paste special-formats" as exemplified in the help '"https://help.libreoffice.org/Calc/Applying_Conditional_Formatting"  Step 5: Copy Cell Style';
now try to change the condition in B3 (if = 0), you will notice that the result is not changed (see attached case1a.jpg) and, continuing to change the condition of B3, the result will be always remain until you change the condition of A3 (it is sufficient to open the window of "Conditional Formatting" and, even without changing anything, clicking OK).

2) Create a new sheet and enter the values and formulas (A1 =230, A2 =225, A3 =A1-A2), copy A1/A3 to A5/A7 and A9/A11 then copy col.A into col.B and col.C, creates a style and apply the conditional formatting ("Format-Conditional Formatting") on A3 (if = 5) and copy it ("Copy - Paste special-formats") on B3 D3 A7 B7 D7 A11 B11 D11;
go to "Conditional Formatting-Manage Conditional Formatting" and the window shows only A3 A7 A11 B3 (see attached case2a.jpg).
Now, trying to change the condition of C3, a warning window will appear (see attached case2b.jpg) that does not appear if you change the condition of B3 (these are only examples, you can prove this even with other cells); however the result in B3 does not change (as already seen at point 1a).

I show that in help "https://help.libreoffice.org/Calc/Conditional_Formatting" there is an incomprehensible statement "Conditional Formatting - Choose Conditional Formatting to define format styles depending on certain conditions. If a style was already assigned to a cell, it remains unchanged. .... ":
I can't understand what it means, it would seem a false statement (see my test point 1) but it could hide some limitation mal implied that would otherwise better clarified.


I hope that you will be able to reproduce the above and find the explanation, it could be the cause of other faults: if it is a bug it should be fixed, if it is intentional it should be highlighted more thoroughly and completely.
Let me ask you also a curiosity: I frequently used in MS Excel the conditional formatting, there it is much less cumbersome and I never had any problems, is there a reason?

Finally, at the margin, I show an error translation in the help (both internet and online):
https://help.libreoffice.org/calc/applying_conditional_formatting" Step 5: Copy Cell Style "To apply the conditional formatting to other cells later:
1.Click one of the cells that has been assigned conditional formatting.
...... "
was incorrectly translated
"1.Fate clic su una delle celle a cui deve essere applicata la formattazione condizionata."
the correct translation is
"1.Fate clic su una delle celle a cui รจ stata applicata la formattazione condizionata."
Comment 13 gmarco 2015-05-07 21:02:35 UTC
I just updated to 4.4.3.2
but nothing has changed, for that I confirm all what I have exposed since my first append.
The initial sheet migrated from MS Excel still shows the wished format if I apply the opposite condition <to Raal with regard of SAMPLE.xls: the file in my harddisk is surely xls, loadin it to OneDrive has been changed as xlsx (???)>.
Last tests (my previous append) show too the same anomalies exposed.
Thanks for your support, awaiting for news.
Comment 14 gmarco 2015-05-08 15:56:16 UTC
Referring to my comment 12
>> 1a) creates a new sheet as above, applies the conditional formatting on A3 and then copy it on B3 using "Copy - Paste special-formats" ....<<
and attached case1a.jpg
I further notice that, saving the file (as xls) in the wrong state and reopening it, the cell B3 too displays now correctly.
Comment 15 gmarco 2015-05-11 10:32:22 UTC
(In reply to raal from comment #9)
> I downloaded your file (it's xlsx). With LO 4.4.2 I see all styles. After
> save as xlsx and reopen, the file look the same as before. Please try with
> LO 4.4 and let us know if you still observe the problem.

Hello to anyone who can explain the anomaly.

Look at  http://1drv.ms/1ItiIbk : there you find two files (they are identical, the .bin is the same of the other original .xls which I renamed the extension, if you download it and rename the extension to .xls you'll get exactly what I have on my harddisk).
Opening it in LOcalc you'll see cells F53-54-66-67 having a conditioned format resulting the opposite of what should be. I don't know and cannot understand how it was happened at the beginning, but so it is now!
All the cells above are conditioned "if not between -12/+12" but for example, cell F54 (containing the value -7) is "red" while it should not be; changing the condition the problem remains: set the condition "if = -7" and check the result, if you want that cell "red" highlighted, you have to set "if not=".

Well, by saving and reopening the file the anomaly remains unchanged.
The only way to resolve the issue is to reset all conditions and set ex-novo style and formatted conditions.
Thanks to anyone who has an answer.
Comment 16 gmarco 2015-05-11 21:07:27 UTC
Created attachment 115508 [details]
same bin on 1drive (see previous comment)

The .bin here attached is the same you can find on 1drive.
Renaming it as .xls you get the original sheet like that I have on my harddisk.
Comment 17 QA Administrators 2016-09-20 09:42:00 UTC Comment hidden (obsolete)
Comment 18 gmarco 2016-09-21 20:29:34 UTC
LO 5.2.1.2 Win10
nothing has changed.

Following on my comment 12:

A - items 1) and 2) are the same already in Bug 91229 and 91312 (nothing has changed, see there)

B - << I show that in help 
"https://help.libreoffice.org/Calc/Conditional_Formatting" there is an incomprehensible statement "Conditional Formatting - Choose Conditional Formatting to define format styles depending on certain conditions. If a style was already assigned to a cell, it remains unchanged. .... ": I can't understand what it means, it would seem a false statement (see my test point 1) but it could hide some limitation mal implied that would otherwise better clarified. >> 

nothing has changed (see new attachment "Bug91070-help_cond_form.JPG")


Following on my comment 15:
nothing has changed 
the link to OneDrive is no more available, but you can download the attachment 115508 [details], then renaming its extension from .bin to .xls you get the original sheet like that I have on my harddisk.
Comment 19 gmarco 2016-09-21 20:31:15 UTC
Created attachment 127529 [details]
screenshot of the help
Comment 20 gmarco 2017-02-23 21:53:04 UTC Comment hidden (off-topic)
Comment 21 Julien Nabet 2017-02-23 21:56:12 UTC Comment hidden (off-topic)
Comment 22 gmarco 2017-02-23 22:16:25 UTC Comment hidden (off-topic)
Comment 23 Aron Budea 2017-03-13 04:21:30 UTC
(In reply to gmarco from comment #22)
> (In reply to Julien Nabet from comment #21)
> > Because you did it at 2015-05-07 17:56:58 UTC (see
> > https://bugs.documentfoundation.org/show_activity.cgi?id=91070)
> > Of course you can just unassign yourself if you want to
> 
> OK, thanks, but I do not know how and why this happened.
> I can not fix the bug, so I think corrected unassign myself.

This happens if on the attachment adding screen you check "take bug and set the bug status to" and set a status there. It's confusing, because it's possible to update the bug status there, but with this step you also assign the bug to yourself.

Additionally, this bug was never confirmed independently, returning status to UNCONFIRMED.
Comment 24 gmarco 2017-03-13 11:09:53 UTC
(In reply to Aron Budea from comment #23)
> (In reply to gmarco from comment #22)
> > (In reply to Julien Nabet from comment #21)
> > > Because you did it at 2015-05-07 17:56:58 UTC (see
> > > https://bugs.documentfoundation.org/show_activity.cgi?id=91070)
> > > Of course you can just unassign yourself if you want to
> > 
> > OK, thanks, but I do not know how and why this happened.
> > I can not fix the bug, so I think corrected unassign myself.
> 
> This happens if on the attachment adding screen you check "take bug and set
> the bug status to" and set a status there. It's confusing, because it's
> possible to update the bug status there, but with this step you also assign
> the bug to yourself.
> 
> Additionally, this bug was never confirmed independently, returning status
> to UNCONFIRMED.

Oooh! more and more confusing !!!
I can confirm that nothing  has changed post my comment 19
Comment 25 Buovjaga 2017-03-15 12:39:37 UTC
(In reply to gmarco from comment #16)
> Created attachment 115508 [details]
> same bin on 1drive (see previous comment)
> 
> The .bin here attached is the same you can find on 1drive.
> Renaming it as .xls you get the original sheet like that I have on my
> harddisk.

I saved it to .ods.

Below are XML snippets from the content.xml. Note that the color #ff9999 is the red we see in for example F54.

Cell 53:

    <table:table-cell table:style-name="ce18" table:formula="of:=[.C38]-[.C39]" office:value-type="float" office:value="-15" calcext:value-type="float">
        <text:p>-15,00</text:p>
    </table:table-cell>

The cell style of F53:

<style:style style:name="ce18" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N2">
    <style:table-cell-properties style:glyph-orientation-vertical="0" fo:background-color="#ff9999" style:cell-protect="protected" style:print-content="true" style:diagonal-bl-tr="none" style:diagonal-tl-br="none" style:text-align-source="value-type" style:repeat-content="false" fo:wrap-option="no-wrap" fo:border="none" style:direction="ltr" fo:padding="0.71mm" style:rotation-angle="0" style:rotation-align="none" style:shrink-to-fit="false" style:vertical-align="automatic" loext:vertical-justify="auto" />
    <style:paragraph-properties css3t:text-justify="auto" fo:margin-left="0mm" style:writing-mode="page" />
    <style:text-properties fo:color="#000000" style:text-outline="false" style:text-line-through-style="none" style:text-line-through-type="none" style:font-name="Courier New" fo:font-size="10pt" fo:font-style="normal" fo:text-shadow="none" style:text-underline-style="none" fo:font-weight="normal" style:font-size-asian="10pt" style:font-style-asian="normal" style:font-weight-asian="normal" style:font-name-complex="Courier New" style:font-size-complex="10pt" style:font-style-complex="normal" style:font-weight-complex="normal" />
    <style:map style:condition="cell-content-is-not-between(-12,+12)" style:apply-style-name="Excel_5f_CondFormat_5f_1_5f_2_5f_1" style:base-cell-address="'RIEPIL.'.F53" />
</style:style>

Row 54:

    <table:table-cell table:style-name="ce19" table:formula="of:=[.C41]-[.C42]" office:value-type="float" office:value="-7" calcext:value-type="float">
        <text:p>-7,00</text:p>
    </table:table-cell>

Cell style of F54 is the same as F53, except the style to be applied by condition is Excel_5f_CondFormat_5f_1_5f_1_5f_1

I don't know what the problem is, but I thought to save some digging time..
Comment 26 tommy27 2017-05-13 07:30:07 UTC
@gmarco
please retest with latest LibO 5.3.3.2
Comment 27 gmarco 2017-05-13 21:15:41 UTC
(In reply to tommy27 from comment #26)
> @gmarco
> please retest with latest LibO 5.3.3.2

Hi Tommy, opening LO I'm still suggested to download 5.2.6
Comment 28 Julien Nabet 2017-05-13 21:18:34 UTC
(In reply to gmarco from comment #27)
> (In reply to tommy27 from comment #26)
> > @gmarco
> > please retest with latest LibO 5.3.3.2
> 
> Hi Tommy, opening LO I'm still suggested to download 5.2.6
LO only proposes version of the same branch. I don't understand why 5.2.7 isn't proposed but no version of a more recent branch will be proposed.

The most simple is still to uninstall your current one, download 5.3.3 and install this last one.
Comment 29 gmarco 2017-05-15 07:54:28 UTC
(In reply to Julien Nabet & Tommaso)
I too do not understand but so it is! However, without the need to uninstall the current one, I installed 5.3.3.2 and tried the bug, but absolutely nothing has changed (see the new attachments): as from my comment #15 cells F53-54-66-67 have a conditioned format resulting the opposite of what should be. All those cells are conditioned "if not between -12/+12" but for example, cell F54 (containing the value -7) is "red" while it should not be; changing the condition the problem remains: set the condition "if = -7" and check the result, it would become "not red" instead remains "red"; if you want that cell  correctly "red" highlighted, you have to set "if not=". 
I also tried to save the XLS file as ODS and reopen it: it does not change anything.

I also do not understand why, after two years, the bug is still UNCONFIRMED, maybe someone gets a different result from mine?
The file (XX-mysampletest.bin) is at your disposal since 2015-05-11 (see my comment #16: The .bin here attached ..... Renaming it as .xls you get the original sheet like that I have on my harddisk), just download it, rename it, open it ... and check it out.
Comment 30 gmarco 2017-05-15 07:56:59 UTC
Created attachment 133325 [details]
xls test in 5.3.3.2

xls test in 5.3.3.2
Comment 31 gmarco 2017-05-15 07:57:51 UTC
Created attachment 133326 [details]
ods test in 5.3.3.2

ods test in 5.3.3.2
Comment 32 Julien Nabet 2017-07-22 09:11:02 UTC
I must recognize I don't understand how these styles work.
Indeed, I opened style "Excel_CondFormat_1_1_1", I don't see "red" color in font effect.
I used it on a cell directly (without using cond format), it doesn't put cell in red.

Then, i clear direct format on F54 and tried to put again the same cond format, it's doesn't work at all.

I uncc myself since won't be able to help here.
Comment 33 gmarco 2017-07-22 13:14:01 UTC
(In reply to Julien Nabet from comment #32)
> I must recognize I don't understand how these styles work.

I don't understand if anyone has succeeded in reproducing the case that I can reproduce repeatedly.
I did not even know if Buovjaga (comment #25) found something.

> I uncc myself since won't be able to help here.
Certainly it's an anomaly, but how can it be resolved?
Comment 34 m.a.riosv 2017-08-04 14:48:26 UTC
Created attachment 135131 [details]
Modifyed sample file

Opening with 5.4.

I see the six 'ConditionalStyle_N', what seems equals, but I don't if excel allow direct formats on the CF.

I think it's only a matter of direct format on the cells, copying to rows 24 to 32 with format to preserve the cf and deleting the CF for B8;B14;B20;C8;C14;C20,
all of this cells now without CF are red background, which is a direct format on the cells.

Clearing direct formatting for B8;B14;B20;C8;C14;C20 an applying a conditional format GreenBackground between -12 and 12, I think it works just fine.
Comment 35 m.a.riosv 2017-08-04 14:51:45 UTC
I have just seen that styles on CF are saved as 'ConditionalStyle_N' (xlsx), I want to guess it's a compatibility matter not an issue.
Comment 36 Buovjaga 2017-08-04 17:52:54 UTC
I showed this to Markus and he commented:
"I just don't understand what the bug reporter wants? at least opening the file I see a conditional format with if value is between -12 and +12 and that seems to work just fine"
Comment 37 Buovjaga 2017-08-04 18:09:27 UTC
Sorry, I will close this per discussion with Markus.

This report has become too much a wandering investigation. If you can describe the problem crystal clearly now, please open a new report. You can CC me to it.
Comment 38 Markus Mohrhard 2017-08-04 19:36:56 UTC
(In reply to Buovjaga from comment #37)
> Sorry, I will close this per discussion with Markus.
> 
> This report has become too much a wandering investigation. If you can
> describe the problem crystal clearly now, please open a new report. You can
> CC me to it.

To add to this. Add a detailed instruction to the bug report with steps that are idiot proof. What do you expect and why? Keep in mind that as a developer we look differently at this report than a user or QA (e.g. I checked the XLS file with a binary XLS inspection tool).

Having detailed steps helps us quickly judge if a report is worth further inspection with additional tools or if other bugs (regressions) are more important right now. Normally the time that is required to understand a bug report is directly proportional to the change of the bug being fixed.