1) lsb_release -rd Description: Ubuntu 11.04 Release: 11.04 2) apt-cache policy libreoffice-calc libreoffice-calc: Installed: 1:3.3.3-1ubuntu2 Candidate: 1:3.3.3-1ubuntu2 Version table: *** 1:3.3.3-1ubuntu2 0 500 http://us.archive.ubuntu.com/ubuntu/ natty-updates/main i386 Packages 100 /var/lib/dpkg/status 1:3.3.2-1ubuntu4 0 500 http://us.archive.ubuntu.com/ubuntu/ natty/main i386 Packages 3) What is expected to happen is when one types in a cell: =(-8)^(1/3) one gets -2 as the answer. 4) What happens instead is one receives #VALUE! as the answer. WORKAROUND: Use Excel in WINE. Microsoft Office Excel 2003 (11.5612.6505) apt-cache policy wine1.3 wine1.3: Installed: 1.3.28-0ubuntu1~ppa1~natty1 Candidate: 1.3.28-0ubuntu1~ppa1~natty1 Version table: *** 1.3.28-0ubuntu1~ppa1~natty1 0 500 http://ppa.launchpad.net/ubuntu-wine/ppa/ubuntu/ natty/main i386 Packages 100 /var/lib/dpkg/status 1.3.15-0ubuntu5 0 500 http://us.archive.ubuntu.com/ubuntu/ natty/universe i386 Packages
Setting this to enhancement. We need to write our own Power function for this. At the moment we are using the c++ standard library functionn which results in an error for negative base and a non integral exponent. Code pointers: http://opengrok.libreoffice.org/xref/core/sc/source/core/tool/interpr5.cxx#1687
Hm after an additional thought I'm not sure if it is really that simple to implement. At the point where we calculate the power we no longer have the information that we had a fraction as exponent. I wonder how excel solves this problem. I suspect that we would have the same problem with ixion.
The problem has been discussed in OOo too; https://issues.apache.org/ooo/show_bug.cgi?id=37129 If you will keep rule a^(r*s) = (a^r)^s you cannot allow negative basis a. Excel does not allow arbitrary fractions but tests only for unit fraction. That is not difficult. The highest such converted number is 1/(2^31-3) in Excel; 1/(2^31-1) fails. So the question is, whether LO should implement Excels behavior for easier converting from Excel. ODF1.2 says "POWER(a,b), where a<=0 and INT(b)!=b, is implementation-defined." in chapter 6.16.46. So the standard allows such solution. If you want to use arbitrary fractions, the class TOOLS_DLLPUBLIC Fraction from fract.hxx in tools might be useful.
WORKAROUND: SIGN(X)*ABS(X)^(1/3)
*** Bug 50596 has been marked as a duplicate of this bug. ***
*** Bug 94568 has been marked as a duplicate of this bug. ***
*** Bug 121398 has been marked as a duplicate of this bug. ***
tested with Version: 6.1.4.0.0+ Build ID: 17c87566e84ac433645f264a9bee4cb5ddbd23a5 CPU threads: 8; OS: Linux 4.19; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); Calc: group threaded Version: 6.2.0.0.alpha1+ Build ID: eb9a223151a00d11ed89a42465663b6e5cd75bd9 CPU threads: 8; OS: Linux 4.19; UI render: default; VCL: kde4; Locale: nl-BE (en_US.UTF-8); UI-Language: en-US Calc: threaded "What is expected to happen is when one types in a cell: =(-8)^(1/3) one gets -2 as the answer." i have the correct answer, no error message
If you have (-8)^(1/3) --> -2 then you also have (-8)^(6004799503160661/2^54) --> -2 And that's clearly wrong. Note: there are no rounding errors in evaluating 6004799503160661/2^54.
Fwiw, this was implemented with https://gerrit.libreoffice.org/plugins/gitiles/core/+/b0fdf6cf11ee46d46925e9cd9fa2768a1b49bb64%5E!/ for bug 69293. The same result -2 for (-8)^(6004799503160661/2^54) There may be no rounding error but an accuracy limit here, see https://www.wolframalpha.com/input/?i=6004799503160661%2F2%5E54 Perhaps reformulating (or even omitting?) the rtl::math::approxEqual() would be an option? Didn't dig into it.
No accuracy problem either. You can enter 6004799503160661 in A1 and widen the column enough that you can see the whole, odd number. You can then add or subtract 1 and see ...662 and ...660 as expected. Scaling by a power of 2 doesn't change that for "double" which is a base-2 representation. x=6004799503160661/2^54 didn't come out of thin air. It is what you get if you round 1/3 to the nearest ieee-854 double number. Once you have x, you have no way of knowing whether it came from 1/3 [plus rounding error] or from 6004799503160661/2^54. Note, that the code you linked to exhibits undefined behaviour if the cast to "int" overflows. That will happen for, for example, (-1)^(2^-64). IIRC, sparc and x86 produce different results for such a cast.
Björn Michaelsen, confirmed result is now -2 in: Version: 6.1.3.2 (x64) Build ID: 86daf60bf00efa86ad547e59e09d6bb77c699acb CPU threads: 8; OS: Windows 10.0; UI render: default; Locale: en-US (en_US); Calc: group threaded
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/559758a35216c0cb852de65d129154947a4d91e8%5E%21 Related: tdf#44076 do not leave cast to int to undefined behaviour It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/+/f4844297c9cdbbf341e3e7d5e2e0c0c18cb40553%5E%21 Related: tdf#44076 use sc::power() in ScMatrix::PowOp() It will be available in 6.3.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Xisco Fauli committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/f07a56498875e67fa2637fd0a73890629024efa8 tdf#44076: sc: Add unittest It will be available in 7.1.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
here is still some air upward, while '=(-8)^(1/3)^(2)' results in 4, just like '=(-8)^(2)^(1/3)', '=(-8)^(2/3)' produces #NUM! error, at least in ver. Version: 7.1.0.0.alpha0+ (x64) Build ID: 0d45380c99c7200075d01860a2315d0ddb450f1c CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: Skia/Raster; VCL: win Locale: de-DE (de_DE); UI: en-US Calc: reopen? new bug? leave for future generations? or am i barking up the wrong tree? btw. the workaroung from @Christopher M. Penalver doesn't hold for this case, at least not with above mentioned ver. @Mike Kaganski has worked out (some of) the logical limitations of such transformations there: https://bugs.documentfoundation.org/show_bug.cgi?id=69293 the annotation 'brackets specify the order' imho doesn't hold, my memories of school math say that there are! rules to deal with and legally change calculation ordering, that way the three terms from the first sentence are equivalent and should give identical results? i have to think about multiple or losing roots or getting additional ones, but at least one result would be nice ... @mwelinder: imho calc stops using exact representations at 2^53-1, already 2^53 isn't exact, and calc does 'more aggresive' rounding than neccessary (down to 14 instead of 15,95 *lol* significant digits), and at 2^54 it's in a range where it doesn't care about 34 less or 66 plus, thus all it's producing consists of approximations and assumptions which approximation best fits user expectations ... i'm in doubt if calc uses real integers anywhere - which could hold up to 2^64 as you pointed out - but uses floats as standard and integer precision is limited to the range where fragment and mantissa balance each other out, given that it would be as legal to calculate -2 for your sample as it's to calculate 2^54 minus 34 to the same 1,8014398509482E+016 representation as 2^54 plus 66, i'd read somewhere that internal values and calculations have better precision and the rounding is only for the final result shown in the sheet, couldn't yet test or prove that ...
(In reply to b. from comment #16) > here is still some air upward, while '=(-8)^(1/3)^(2)' results in 4, just > like '=(-8)^(2)^(1/3)', '=(-8)^(2/3)' produces #NUM! error, at least in ver. > > Version: 7.1.0.0.alpha0+ (x64) > Build ID: 0d45380c99c7200075d01860a2315d0ddb450f1c > CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: > Skia/Raster; VCL: win > Locale: de-DE (de_DE); UI: en-US > Calc: > > reopen? new bug? leave for future generations? or am i barking up the wrong > tree? > > btw. the workaroung from @Christopher M. Penalver doesn't hold for this > case, at least not with above mentioned ver. > > @Mike Kaganski has worked out (some of) the logical limitations of such > transformations there: > https://bugs.documentfoundation.org/show_bug.cgi?id=69293 > > the annotation 'brackets specify the order' imho doesn't hold, my memories > of school math say that there are! rules to deal with and legally change > calculation ordering, that way the three terms from the first sentence are > equivalent and should give identical results? i have to think about multiple > or losing roots or getting additional ones, but at least one result would be > nice ... > > @mwelinder: imho calc stops using exact representations at 2^53-1, already > 2^53 isn't exact, and calc does 'more aggresive' rounding than neccessary > (down to 14 instead of 15,95 *lol* significant digits), and at 2^54 it's in > a range where it doesn't care about 34 less or 66 plus, thus all it's > producing consists of approximations and assumptions which approximation > best fits user expectations ... > > i'm in doubt if calc uses real integers anywhere - which could hold up to > 2^64 as you pointed out - but uses floats as standard and integer precision > is limited to the range where fragment and mantissa balance each other out, > > given that it would be as legal to calculate -2 for your sample as it's to > calculate 2^54 minus 34 to the same 1,8014398509482E+016 representation as > 2^54 plus 66, > > i'd read somewhere that internal values and calculations have better > precision and the rounding is only for the final result shown in the sheet, > couldn't yet test or prove that ... This bug is confirmed resolved a long time ago, and in latest Calc below. If you find you are experiencing a bug in LibreOffice, please file a new report (not make comments in a confirmed resolved and closed report). Version: 7.0.2.2 (x64) Build ID: 8349ace3c3162073abd90d81fd06dcfb6b36b994 CPU threads: 8; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: threaded
newbie-02@gmx.de, please see my previous comment. Thanks!
(In reply to Christopher M. Penalver from comment #18) > newbie-02@gmx.de, please see my previous comment. Thanks! no fullquotes please, the bug was old, but the work on it quite recent, the fix is - imho - incomplete as it doesn't work for '=-8^(2/3)' which is similar as it would only work for '-8...' and mis- or not calculate '=-27^(1/3)', (in most cases the actual provided sample should be understood as a sample! for similar misbehaviour on other values as well, and fixes should cover the whole field, not the single flower ... ) '=-8^(2/3)' and '=-27^(2/3)' are! 'calculateable terms', even in calc, if you rearrange them to '=-8^2^(1/3)' or '=-8^(1/3)^2', imho that question is very very near to this bug, thus i added it here (and because @xisco likes to close new reports from me as duplicate even when they report about new! flavours of old misbehaviour) ok? tested with: Version: 7.1.0.0.alpha0+ (x64) Build ID: 586f6abee92af3cdabdce034b607b9a046ed3946 CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win Locale: de-DE (de_DE); UI: en-US Calc:
newbie-02@gmx.de, please don't reopen a closed report that's confirmed fixed. The scope of this closed report has nothing to do with what you are talking about. If you have an issue, file a new report (not reopen this one). Thanks!
Why did you implement mathematical inconsistency in Calc? Because MS did the same in Excel? Now a^b != exp(b*ln(a)) And computing (-8)^x with x -> 1/3 gives 0,3 #NUM ! 0,33 #NUM ! 0,333 #NUM ! 0,3333 #NUM ! 0,33333 #NUM ! 0,333333 #NUM ! 0,3333333 #NUM ! 0,33333333 #NUM ! 0,333333333 #NUM ! 0,3333333333 #NUM ! 0,33333333333 #NUM ! 0,333333333333 #NUM ! 0,3333333333333 #NUM ! 0,33333333333333 #NUM ! 0,333333333333333 -2 The last result is wrong because 0,333333333333333 != 1/3 Even better: 0,333333333333334 -2 :-( And the function f(x) = (-8)^x is not continuous. You are confusing cubic root and the exponentiation function. a^b is not defined for a ≤ 0 I am interesting to know a use case where we have to compute the cubic root of a negative number without knowing in advance that the number is negative. Best regards. JBF
(In reply to Jean-Baptiste Faure from comment #21) it's a little overstretching the OT, but as you ask and since many similar questions are worrying me ... > Why did you implement mathematical inconsistency in Calc? Because MS did the > same in Excel? that could be discussed as one of multiple meaningful ideas - not necessarily good or the best, but most decisions are two faced. IMHO there have been decisions to - use IEEE doubles - they are fast but not exact, - in Excel to - try to - cover some of the resulting issues by restricting to 15 sig. decimal digit precision ( alas different internally and for the GUI inducing new issues ), - in Calc topping that by implementing an 'approximal' concept ( questionable and not sufficient attempt to cover issues resulting from the above ), allowing 4 bit ( up to 30 ULP in some cases ) imprecision, and thus in LO Calc: '=0.333333333333334=0.333333333333333' -> 'TRUE' and '=0.333333333333334-0.333333333333333' -> '0' while '=RAWSUBTRACT(0.333333333333334,0.333333333333333)' -> '9.99200722162641E-16 ' my gut feeling says that it is logically impossible to do consistent mathematics on such a basis, nevertheless it is tried again and again with pleasure. the effect of all these attempts for the OP problem is - in LO Calc: '1/3' = '0.333333333333333' = '0.333333333333334' = '6004799503160661/2^54' 'approximate math' on a high level, > Now a^b != exp(b*ln(a)) there is one math which is consistent to high degree: school math, IMHO implementing a variant with some deviations is basically difficult to build fully consistent, thus expect fails here and there ... > You are confusing cubic root and the exponentiation function. a^b is not > defined for a ≤ 0 I vague remember that there is a valid concept to write roots as exponentiation with fractions, but restricted to 'finally shortened fractions of exact integers if you want to stay with rational results' or similar? with irrational - complex - roots you can use all rationals as exponents? 'accuracy' ... '2^54' is accurate only from a binary POV, from a decimal POV it's beyond the limits where values can be represented to integer precision with IEEE doubles, and thus the representative for a range, [ 18014398509481983 .. 18014398509481986 )? has the 'alternate' prime factorization of '3*3*3*3*7*19*73*87211*262657' or can be divided by 6004799503160661 to a clean 3. a justified result in a math system where '= 3 * 6004799503160661' -> '1801439850948198**4**'. accounting that and calculating with the shortened figure '1/3': '=(-8)^(1/3)' -> '-2' is! a school mathematical meaningful result ... in the limits of how accurate binary-FP-math can emulate decimal calculations. If you are looking for a spreadsheet with better precision and less - but not yet none - inconsistencies and can live with a little reduced comfort: have a look at gnumeric, IMHO calc could do good orienting more towards gnumeric and less towards Excel, but a conversion would be an enormous piece of work.