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.