Description: if you enter the following formula =(-5)^x where x is 0.2 the reported result is 1.3797297..., which is wrong. The true value is 1.116225 + 0.810985*i (a complex number) The wrong result is reported for any negative number, provided the exponent is 0.2 (or 1/5). For any other exponent, Calc reports #NUM! error message, which is OK, as the result is a complex number. The error persists as long as the "y" in y^x is a negative number. Steps to Reproduce: 1. Open calc and enter the following formula into any cell 2. =(-5)^0.2 1.3797297... is reported as a result 3. =(-5)^0.33 Actual Results: 2. 1.3797297... wrong 3. #NUM! correct (A result is a complex number) Expected Results: 2. #NUM! (the formula evaluates to a complex number1.116225 + 0.810985*i and only a real number can be displayed in the cell unless complex arithmetics is used) 3. #NUM! Reproducible: Always User Profile Reset: No Additional Info: There's exactly the same bug in Excel (Excel 365, v. 16.57) Google Sheets handle the calculation properly #NUM! with an explanatory note "POWER evaluates to an imaginary number." The number reported by Calc and Excel with exponent 0.2 is the absolute value of the complex result.
The bug was initially reported in Excel (unknown version) on a PC under Windows
@Eike, I thought you might be interested in this issue
Can't say about math but LO 4.4 would give #VALUE! and this starter from 5.0.
Apparently a result of intentionally wanting to evaluate odd roots of negative numbers, see bug 69293 (and related 44076). It's arguable whether that is actually "a good thing" or not.. Maybe Regina has an opinion?
(In reply to Michal Svoboda from comment #0) > 1. Open calc and enter the following formula into any cell > 2. =(-5)^0.2 1.3797297... is reported as a result > > Actual Results: > 2. 1.3797297... wrong What makes someone believe that this is wrong? There are 5 (five) 5th roots of -5, so why would someone claim that returning one of those is correct, and returning another is wrong?
(In reply to Michal Svoboda from comment #0) > 1. Open calc and enter the following formula into any cell > 2. =(-5)^0.2 1.3797297... is reported as a result > > Actual Results: > 2. 1.3797297... wrong Oh - I see that I can't repro this. On Version: 7.3.2.2 (x64) / LibreOffice Community Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0 CPU threads: 12; OS: Windows 10.0 Build 19044; UI render: default; VCL: win Locale: ru-RU (ru_RU); UI: en-US Calc: the result in not "1.3797297", but "-1.3797297" - correctly returning the *negative* result, which gives -5 if multiplied by itself five times.
Excel gives -1.37972966146122 as result of =(-5)^0.2. LibreOffice does the same. ODF defines operator ^ with the POWER function. Both Excel and LibreOffice behaves that way. We should keep the current behavior. ODF 1.3 has specified POWER(A,B), where A ≤ 0 and INT(B) != B, is implementation-defined. So our current behavior is valid against ODF. An "implementation-defined" behavior means, that the application must document the behavior. Our page for this is https://wiki.documentfoundation.org/Development/ODF_Implementer_Notes/List_of_LibreOffice_ODF_implementation-defined_items. In section "Functions" it refers to the Help, for LO 7.3 https://help.libreoffice.org/7.3/en-US/text/scalc/01/04060106.html?DbPAR=CALC and https://help.libreoffice.org/7.3/en-US/text/scalc/01/04060199.html?DbPAR=CALC But there the description of this behavior is missing for the POWER function and the ^ operator as well. So this is actually a documentation bug.
> why would someone claim that returning one of those is correct, > and returning another is wrong? There are actually good reasons for picking 1.116225+0.810985*i, but only if you already think of it as a complex-valued function. It is what you get with x^y defined as exp(y*log(x)) and the standard branch cut of log. Mathematica follows that, fwiw. If you think of x^y as a real valued function then -1.3 is the only choice. And thinking of x^y this way is quite reasonable for a spreadsheet. For completeness, if you think of x and y as IEEE-854 numbers, then an error is the only reasonable result. y here is 3602879701896397/2^54. That's an even denominator. But in none of those cases does it seem reasonable to bite the head off the reporter.
- one of the five fifth roots of a negative number (-5) is real (-1^0.2 gives also a real root -1), but not giving all five roots or otherwise notifying the user that there are five fifth roots is an obvious error from the point of view of mathematics. Spreadsheets should do correct maths. - likewise, one of the 7th roots of a negative number is real, and one of the 3rd roots... however, these are reported with #NUM! - it seems that the number 0.2 (1/5) is somehow special in this case (the same holds for 2/10, 4/20) - the exponent of 0.4 (2/5) gives an error message #NUM!, while it evaluates always to a positive real number in maths =(-5)^(2/5), i.e. (-5)^2*(1/5). It seems someone tried to implement fractional powers of negative numbers for some the special cases and the result was at best incomplete...
(In reply to Michal Svoboda from comment #9) > not giving all five roots or otherwise > notifying the user that there are five fifth roots is an obvious error from > the point of view of mathematics. Spreadsheets should do correct maths. So, do you agree that SQRT(x) must return an error for all x, or a matrix of two numbers? And current convention of returning a single result is plain wrong, because who on Earth needs a single root? ;-) (Of course, the implementation is incomplete, and needs some love. No doubt in that.)
There are more aspects: - giving anything else than a single real number in LO Calc is next to impossible - there are some complex arithmetics functions included within the LO list of functions, anyway, this implementation is somewhat awkward (from my point of view)and, in the case of the fifth root there should be a matrix of five results - all fractions with an odd denominator should be equal, but some of them are more equal than others (1/5, 2/10, 4/20) I'd suggest implementing (if that is possible) - more detailed error message for all instances where any root of a negative number is calculated. This would solve the problem on a short time scope (while keeping compatibility with the same bug in Excel) - returning a real root, where one exists (i.e. also for exponents like 1/3, 1/7, etc.), and notifying the user there are other (complex) roots, too - you're in the complex number domain now boy, could add to clarity and consistency of the arithmetics inside the sheet - or marking all calculations potentially resulting in complex numbers as #ERR! with a comment - you're trying something that is outside the sheet range of real numbers, boy
(In reply to Michal Svoboda from comment #11) > - returning a real root, where one exists (i.e. also for exponents like 1/3, > 1/7, etc.) I agree. > and notifying the user there are other (complex) roots, too - > you're in the complex number domain now boy, could add to clarity and > consistency of the arithmetics inside the sheet > > - or marking all calculations potentially resulting in complex numbers as > #ERR! with a comment - you're trying something that is outside the sheet > range of real numbers, boy Which specific *user problem* is this intended to solve, other than satisfying someone's sense of beauty? I suppose that there is no one who uses Calc, who would in practice benefit from this. Spreadsheets are *specific* domain, with some conventions and simplifications. We also do not try to implement physics of time, when implement time functions; and this one looks very much trying to implement in spreadsheets something that is more suitable for computer algebra systems.
> Which specific *user problem* is this intended to solve, other than satisfying someone's sense of beauty? I suppose that there is no one who uses Calc, who would in practice benefit from this. There are many formulae in the realm of e.g. physics, which require the calculation of fractional roots. Evaluation of the parameters involved in such a formula may lead to the base being negative, this usually indicates an error in the entire chain of calculations or in the values entered - the practical problem here is to analyze the chain of calculations for the error. Proper indication of the error condition helps ensure the calculation is robust. > Spreadsheets are *specific* domain, with some conventions and simplifications. We also do not try to implement physics of time, when implement time functions; and this one looks very much trying to implement in spreadsheets something that is more suitable for computer algebra systems. Agreed, adding up to the point error messages actually prevents any need of implementing things that cannot be (easily) implemented in spreadsheets and sends a message "this is not a proper tool for your problem", i.e. avoiding false expectations.
appropriate to change the subject to 'Calc ...'?
Dear Michal Svoboda, 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 with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. 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) from https://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: https://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
The issue still partly persists, but the interpretation of fractional powers of negative numbers is more of a question of interpretation. Google Sheets consistently flags formulae like (-5)^(1/x), where x is an integer, as an error, even when real roots exist. Excel (and Calc) reports real roots when x = 5; otherwise, it reports an error. Calc also reports real roots when x is an ODD number. This is, of course, inconsistent with complex mathematics, but again, the problem is that Spreadsheets cannot perform complex mathematics (more than one result of a formula) in principle. The ways to flag this are open to interpretation. For me, you can close the issue.