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 ...'?