Ubuntu
libreoffice package

[Upstream] IF function not evaluating correctly with cell borders in .ods

Bug #1263326 reported by Ray DeCampo
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
LibreOffice
Fix Released
Medium
libreoffice (Ubuntu)
Fix Released
Medium
Unassigned

Bug Description

1) lsb_release -rd
Description: Ubuntu Trusty Tahr (development branch)
Release: 14.04

2) apt-cache policy libreoffice-calc
libreoffice-calc:
  Installed: 1:4.1.3-0ubuntu2
  Candidate: 1:4.1.3-0ubuntu2
  Version table:
 *** 1:4.1.3-0ubuntu2 0
        500 http://us.archive.ubuntu.com/ubuntu/ trusty/main amd64 Packages
        100 /var/lib/dpkg/status

3) What is expected to happen via a terminal:
cd ~/Desktop && wget https://bugs.launchpad.net/df-libreoffice/+bug/1263326/+attachment/3934242/+files/bug-format-affects-value.ods && localc --nologo bug-format-affects-value.ods

is that cells C1 and C2 both show Passed.

4) What happens instead is that cell C2 shows Failed.

WORKAROUND: Insert a row, and then remove the row.

ProblemType: Bug
DistroRelease: Ubuntu 13.10
Package: libreoffice-calc 1:4.1.3-0ubuntu1
ProcVersionSignature: Ubuntu 3.11.0-14.21-generic 3.11.7
Uname: Linux 3.11.0-14-generic x86_64
NonfreeKernelModules: nvidia
ApportVersion: 2.12.5-0ubuntu2.2
Architecture: amd64
Date: Sat Dec 21 07:59:29 2013
InstallationDate: Installed on 2012-11-18 (397 days ago)
InstallationMedia: Ubuntu 12.10 "Quantal Quetzal" - Release amd64 (20121017.5)
MarkForUpload: True
ProcEnviron:
 LANGUAGE=en_US
 PATH=(custom, no user)
 XDG_RUNTIME_DIR=<set>
 LANG=en_US.UTF-8
 SHELL=/bin/bash
SourcePackage: libreoffice
UpgradeStatus: Upgraded to saucy on 2013-11-10 (40 days ago)

See original description
Revision history for this message
Ray DeCampo (rdecampo) wrote :
penalvch (penalvch)
description: updated
description: updated
penalvch (penalvch)
description: updated
penalvch (penalvch)
description: updated
description: updated
summary: - Formula values not updated automatically
+ #VALUE doesn't propagate correctly subtracting text from numbers
penalvch (penalvch)
Changed in libreoffice (Ubuntu):
importance: Undecided → Low
status: New → Triaged
tags: added: trusty
Revision history for this message
Ray DeCampo (rdecampo) wrote :

Christopher, the calculation in H6 of H5-D6 is protected by the IF() function, ensuring that we do not use H5-D6 when D6 is "". In fact, the cell has the proper value when other things are changed that should not affect this bug. As I stated in my original description, this includes ordering the rows by date or re-copying the formulas in D6 and E6 to themselves. This proves that the formula in H6 is fine. Furthermore the formula for H4 and H5 are the same as H6 except for the row numbers and as you can see there is no #VALUE issue with those cells. This behavior is new, I have been using this spreadsheet since 2001 in OpenOffice/LibreOffice and never had this issue.

Revision history for this message
Ray DeCampo (rdecampo) wrote :

Further investigation reveals that the issue is that the cell D6 is incorrectly evaluated to the value " " instead of the value "". This can be verified by copying the cell D6 and pasting just the text into another cell. So perhaps it is related to the same issue that is causing the cells in the F column to change from "x" to "x " and then "x ".

Revision history for this message
penalvch (penalvch) wrote :

Ray DeCampo, thank you for your comments. I'm comparing the results to Excel from a feature parity expectation perspective, so this is why my expectations were different. However, LibreOffice has on a number of occasions developed past Excel for the better (ex. making functions more precise than equivalent ones in Excel, letting functions calculate larger values than Excel functions, etc.), and this would be an example of that.

In Microsoft Office Excel Viewer (12.0.6219.1000 MSO (12.0.6213.1000), it propagates the #VALUE error due to the malformed subtracting of numbers and text. However, LibreOffice has done work to develop past this limitation. So, your expectation would be correct, in that cell H6 should show $4,081.03 despite the malformed subtraction. I'll correct the Bug Description to this.

Hence, the issue you are reporting is an upstream one. It would be nice if somebody having it could send the bug to the developers of the software by following the instructions verbatim at http://wiki.documentfoundation.org/BugReport . If you have done so, please tell us the number of the upstream bug (or the link), so we can add a bugwatch that will inform us about the status. Thanks in advance.

Helpful bug reporting tips:
https://help.ubuntu.com/community/ReportingBugs

description: updated
tags: added: regression-release
Revision history for this message
In , Ray DeCampo (rdecampo) wrote :

Created attachment 91124
Example spreadsheet as in the bug description

Problem description:

When adding formatting, e.g. borders, to a cell, the string value of the cell is affected.

Steps to reproduce:

See attached spreadsheet. The first line has no formatting and behaves as expected. The second line has a border on cell B2 and the value of that cell is affected.

Current behavior:

Cell C2 displays the value "Failed".
If the value of cell B2 is copied and pasted as text the value pasted is "foobar ".

Expected behavior:

Cell C2 displays the value "Passed" like the cell C1.
If the value of cell B2 is copied and pasted as text the value pasted is "foobar".

Operating System: Ubuntu
Version: 4.1.3.2 release

Revision history for this message
In , Ray DeCampo (rdecampo) wrote :

Created attachment 91124
Example spreadsheet as in the bug description

Problem description:

When adding formatting, e.g. borders, to a cell, the string value of the cell is affected.

Steps to reproduce:

See attached spreadsheet. The first line has no formatting and behaves as expected. The second line has a border on cell B2 and the value of that cell is affected.

Current behavior:

Cell C2 displays the value "Failed".
If the value of cell B2 is copied and pasted as text the value pasted is "foobar ".

Expected behavior:

Cell C2 displays the value "Passed" like the cell C1.
If the value of cell B2 is copied and pasted as text the value pasted is "foobar".

Operating System: Ubuntu
Version: 4.1.3.2 release

Revision history for this message
In , Ray DeCampo (rdecampo) wrote :

I forgot to clarify in the original description, the issue began manifesting when I upgraded from Ubuntu 13.04 to 13.10. I believe the version of LibreOffice for 13.04 is 4.0.2.

Revision history for this message
In , Ray DeCampo (rdecampo) wrote :

I forgot to clarify in the original description, the issue began manifesting when I upgraded from Ubuntu 13.04 to 13.10. I believe the version of LibreOffice for 13.04 is 4.0.2.

Revision history for this message
Ray DeCampo (rdecampo) wrote :

Further research reveals the issue is with formatting, in particular borders, affecting the value of cells. I will attach a new spreadsheet demonstrating the issue. I have also filed a bug at LibreOffice:

https://www.libreoffice.org/bugzilla/show_bug.cgi?id=72969

Steps to reproduce:

See attached spreadsheet (bug-format-affects-value.ods). The first line has no formatting and behaves as expected. The second line has a border on cell B2 and the value of that cell is affected.

Current behavior:

Cell C2 displays the value "Failed".
If the value of cell B2 is copied and pasted as text the value pasted is "foobar ".

Expected behavior:

Cell C2 displays the value "Passed" like the cell C1.
If the value of cell B2 is copied and pasted as text the value pasted is "foobar".

penalvch (penalvch)
summary: - #VALUE doesn't propagate correctly subtracting text from numbers
+ #VALUE propagates incorrectly subtracting text from numbers
summary: - #VALUE propagates incorrectly subtracting text from numbers
+ IF function not evaluating correctly with cell borders in .ods
penalvch (penalvch)
description: updated
Changed in libreoffice (Ubuntu):
importance: Low → Medium
Revision history for this message
In , penalvch (penalvch) wrote :

Raymond DeCampo, thank you for reporting this. Would you please be able to bibisect this problem following https://wiki.documentfoundation.org/QA/HowToBibisect ? This would help tremendously in resolving this bug.

Revision history for this message
In , penalvch (penalvch) wrote :

Raymond DeCampo, thank you for reporting this. Would you please be able to bibisect this problem following https://wiki.documentfoundation.org/QA/HowToBibisect ? This would help tremendously in resolving this bug.

penalvch (penalvch)
summary: - IF function not evaluating correctly with cell borders in .ods
+ [Upstream] IF function not evaluating correctly with cell borders in
+ .ods
Bug Watch Updater (bug-watch-updater)
Changed in df-libreoffice:
importance: Unknown → Medium
status: Unknown → Confirmed
Revision history for this message
In , Libreoffice-0 (libreoffice-0) wrote :

I just tested this using the latest versions of 4.1 and 4.2 (and master), and this no longer happens.

To verify, you need to hit F9 after loading the submitted document because the formula results are initially cached. Once the cells get recalculated, the reported problem no longer happens.

Revision history for this message
In , Kohei-d (kohei-d) wrote :

I just tested this using the latest versions of 4.1 and 4.2 (and master), and this no longer happens.

To verify, you need to hit F9 after loading the submitted document because the formula results are initially cached. Once the cells get recalculated, the reported problem no longer happens.

penalvch (penalvch)
description: updated
Revision history for this message
In , penalvch (penalvch) wrote :

Kohei Yoshida, thanks for taking a look at this. Unfortunately, the issue noted in the Description is reproducible in:
Version: 4.2.3.3
Build ID: 882f8a0a489bc99a9e60c7905a60226254cb6ff0
Microsoft Windows Vista Business x86 6.0.6002 Service Pack 2 Build 6002

As well, F9 does not update the calculation.

Revision history for this message
In , penalvch (penalvch) wrote :

Kohei Yoshida, thanks for taking a look at this. Unfortunately, the issue noted in the Description is reproducible in:
Version: 4.2.3.3
Build ID: 882f8a0a489bc99a9e60c7905a60226254cb6ff0
Microsoft Windows Vista Business x86 6.0.6002 Service Pack 2 Build 6002

As well, F9 does not update the calculation.

Revision history for this message
In , Jmadero-dev (jmadero-dev) wrote :

There is some confusion about this issue as Kohei couldn't reproduce. Moving to UNCONFIRMED to get fresh eyes on it. Thanks for understanding.

Revision history for this message
In , Jmadero-dev (jmadero-dev) wrote :

There is some confusion about this issue as Kohei couldn't reproduce. Moving to UNCONFIRMED to get fresh eyes on it. Thanks for understanding.

Revision history for this message
In , penalvch (penalvch) wrote :

The steps noted in the Description https://bugs.freedesktop.org/show_bug.cgi?id=72969#c0 are still confirmed verbatim in MASTER:
Version: 4.4.0.0.alpha1+
Build ID: b7d8a58ff2698ffc6e22943f64aa97c5ea253bd9
TinderBox: Win-x86@42, Branch:master, Time: 2014-11-05_00:40:38
Microsoft Windows Vista Business x86 6.0.6002 Service Pack 2 Build 6002

As I'm a member of LibreOffice QA, I've marked myself the QA contact if you would have any further questions on the scope of this report.

Revision history for this message
In , penalvch (penalvch) wrote :

The steps noted in the Description https://bugs.freedesktop.org/show_bug.cgi?id=72969#c0 are still confirmed verbatim in MASTER:
Version: 4.4.0.0.alpha1+
Build ID: b7d8a58ff2698ffc6e22943f64aa97c5ea253bd9
TinderBox: Win-x86@42, Branch:master, Time: 2014-11-05_00:40:38
Microsoft Windows Vista Business x86 6.0.6002 Service Pack 2 Build 6002

As I'm a member of LibreOffice QA, I've marked myself the QA contact if you would have any further questions on the scope of this report.

Revision history for this message
In , Jmadero-dev (jmadero-dev) wrote :

Apologies Christopher - didn't recognize your name :) Thanks for confirming!

Revision history for this message
In , Jmadero-dev (jmadero-dev) wrote :

Apologies Christopher - didn't recognize your name :) Thanks for confirming!

Revision history for this message
In , Qa-admin-q (qa-admin-q) wrote :

** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present on a currently supported version of LibreOffice (5.0.4 or later)
   https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior

the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case)

If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:

1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3)

http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug

3. Leave a comment with your results.

4a. If the bug was present with 3.3 - set version to "inherited from OOo";

4b. If the bug was not present in 3.3 - add "regression" to keyword

Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa

Thank you for your help!

-- The LibreOffice QA Team This NEW Message was generated on: 2015-12-20

Revision history for this message
In , Qa-admin-q (qa-admin-q) wrote :

** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present on a currently supported version of LibreOffice (5.0.4 or later)
   https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior

the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case)

If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:

1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3)

http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug

3. Leave a comment with your results.

4a. If the bug was present with 3.3 - set version to "inherited from OOo";

4b. If the bug was not present in 3.3 - add "regression" to keyword

Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa

Thank you for your help!

-- The LibreOffice QA Team This NEW Message was generated on: 2015-12-20

Revision history for this message
In , Qa-admin-q (qa-admin-q) wrote :

** Please read this message in its entirety before responding **

To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year.

There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present.

If you have time, please do the following:

Test to see if the bug is still present on a currently supported version of LibreOffice
(5.1.6 or 5.2.3 https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the version of LibreOffice and
your operating system, and any changes you see in the bug behavior

If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave
a short comment that includes your version of LibreOffice and Operating System

Please DO NOT

Update the version field
Reply via email (please reply directly on the bug tracker)
Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not
appropriate in this case)

If you want to do more to help you can test to see if your issue is a REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3)

http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to "inherited from OOo";
4b. If the bug was not present in 3.3 - add "regression" to keyword

Feel free to come ask questions or to say hello in our QA chat: http://webchat.freenode.net/?channels=libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug-20170103

Revision history for this message
In , Ray DeCampo (rdecampo) wrote :

On Fedora 25, LO build ID 5.2.3.3-11.fc25, I still see the bug as originally described.

Marking with the regression keyword as the bug was not present in version 4.0.2.

Revision history for this message
In , Eike Rathke (erack) wrote :

Cell B2 has been stored to file with the result office:string-value="foobar " i.e. with trailing spaces, which also on Paste Special are preserved, hence the condition B2="foobar" does not match. This has nothing to do with the IF function. Unless B2 is recalculated (F9 on cell, Shift+Ctrl+F9 or recalculate after load through Tools Options) to fix the result value, there's nothing we can do.

If this was a specific bug in an earlier version (I have no idea how this could be even related to cell borders) it got fixed in the mean time. Closing.

Revision history for this message
Marcus Tomlinson (marcustomlinson) wrote :

Synchronising bug status with upstream.

Changed in libreoffice (Ubuntu):
status: Triaged → Fix Released
Changed in df-libreoffice:
importance: Medium → Unknown
status: Confirmed → Unknown
Bug Watch Updater (bug-watch-updater)
Changed in df-libreoffice:
importance: Unknown → Medium
status: Unknown → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.