[Bug 1263326] Re: #VALUE doesn't propagate correctly subtracting text from numbers
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. ** Bug watch added: LibreOffice Bugzilla #72969 https://www.libreoffice.org/bugzilla/show_bug.cgi?id=72969 ** Attachment added: Simplified example spreadsheet demonstrating the issue https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1263326/+attachment/3934242/+files/bug-format-affects-value.ods -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1263326 Title: #VALUE doesn't propagate correctly subtracting text from numbers To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1263326/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1263326] Re: #VALUE doesn't propagate correctly subtracting text from numbers
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. -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1263326 Title: #VALUE doesn't propagate correctly subtracting text from numbers To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1263326/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1263326] Re: #VALUE doesn't propagate correctly subtracting text from numbers
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 . -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1263326 Title: #VALUE doesn't propagate correctly subtracting text from numbers To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1263326/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
[Bug 1263326] Re: #VALUE doesn't propagate correctly subtracting text from numbers
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 changed: 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/ubuntu/+source/libreoffice/+bug/1263326/+attachment/3933694/+files/bug.ods localc --nologo bug.ods - is that cell H6 shows #VALUE no matter what, given it is a result of - subtracting text from a number. + is that cell H6 shows the expected value of $4081.03. - 4) What happens instead is if one inserts a row, and then removes the - row, it resolves to a number value. + 4) What happens instead is it shows #VALUE. + + WORKAROUND: if one inserts a row, and then removes the row, it shows the + expected $4081.03. WORKAROUND: Change all formulas containing: to the number zero: 0 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) ** Tags added: regression-release -- You received this bug notification because you are a member of Ubuntu Bugs, which is subscribed to Ubuntu. https://bugs.launchpad.net/bugs/1263326 Title: #VALUE doesn't propagate correctly subtracting text from numbers To manage notifications about this bug go to: https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/1263326/+subscriptions -- ubuntu-bugs mailing list ubuntu-bugs@lists.ubuntu.com https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
