Re: Cube Root of Negative Numbers: ERROR

[John Machin <sjmachin lexicon net>]

On 23/02/2007 5:41 AM, Leonard Mada wrote:
> *The Cube Root of Negative Numbers returns an ERROR*
>
> It seems that gnumeric cannot calculate the cube root of negative 
> numbers. I have tested a couple of programs and found these results:
>
> =POWER(-27, 1/3) returns

That is *not* attempting to calculate the cube root of a negative 
number. It is attempting to calculate raising a negative number to some 
fractional power.

Note: if the software is using 64-bit IEEE754 floating point, 1/3 will 
be 0.333....3331 (decimal) -- it can't be represented exactly. In fact 
for all odd n greater than 1, 1/n can't be represented exactly in binary 
floating point with any number of bits of precision.

>   #VALUE! in OOo 2.1
>   #NUM! in gnumeric 1.7.6
>   "-3" in MS Excel XP => TRUE

Does it *really* return "-3"? I've just tried it with Excel 2003, and 
what it wrote to disk was the binary floating point equivalent of 
-2.9999999999999996 i.e. one bit out in the mantissa.

> R returns an ERROR, too: (-27)^(1/3) returns "NaN".

Python raises an exception: "ValueError: negative number cannot be 
raised to a fractional power"

C: It is written in K&R 2nd edition on page 251 re the library function 
pow(x, y): "A domain error occurs if x == 0 and y <= 0, or if x < 0 and 
y is not an integer."

> Seems, a lot of software gets it wrong, though Excel manages to calculate it.

Ah yes, but everything is not as it seems ;-)

This reminds me of the joke about the proud mother watching the local 
militia regiment march by on some festive occasion and exclaiming "Look! 
My Billy Bob's the only one marching in step!".

> I hope therefore, that this gets corrected in gnumeric, too. Should I post it as a BUG?

Yes. But don't bother the gnumeric crew. Send it to 
billybob gates microsoft com

*Stop press* Don't even bother with that. I've just looked up the ECMA 
standard for "office open XML" (i.e. Microsoft Office 2007 XML): the 
POWER function is described in part 4 section 3.17.7.252. I quote: 
"However if ... The value of x is negative and y is not a whole number, 
#NUM! is returned". Looks like the drill sergeant gave Billy Bob a clip 
around the ears :-)

HTH,
John