Capture d’écran 2013-03-27 à 15.36.15

Rationality, and MS Excel (and other calculators)

This morning, Mathieu had a nice experience in his course on computational method in actuarial science. But let us start with some mathematical formal definitions.

First, recall that^x is – somehow – a standard expression. No one should be surprised to see such an expression. Generally (as explained in… ), this function is defined only when\in\mathbb{R}_+. The idea is that the definition of^x is that^x%20=%20\exp\left(x\log[y]\right)

And it is a definition. Such a function exists only if\in\mathbb{R}_+ (maybe excluding This would be a standard definition in real-analysis.

Now, this ‘power’ function appears also in complex analysis, when dealing with unit roots. From instance, if^{\frac{1}{k}}e^{i%20\frac{2n\pi}{k}}, where\in\mathbb{R}_+ and\in\mathbb{N}_\star, for some\in\mathbb{N}, then^k=y. Thus, in complex-analysis it might be more complex to define properly^x since it might not be unique. But we can relate (sometimes, when is the inverse of an integer, or maybe a rational number ?) with roots of polynomial functions. So far, nothing new…

Let us get back to Mathieu’s problem. Actually, in his course, he wanted to compute^{\frac{1}{3}}. With a French version of Excel, entering

you do get If you look at the ‘help’ window, you have some more details

It looks like this hat function can be used to define objects such as^x. But with

you get

(meaning that this is a problem…). It is also possible to use the power (puissance in French) function of Excel,

Here, you also get

The weird part here is that, in the ‘help’ window, you can read that this power function can be used with any number in\mathbb{R}.

Another point… what about  ? Somehow, it is just the square of the previous one (with the fraction)… Here, typing

you get

(similarly with the power function). So clearly, it is not that simple to use this power function. Now, if you use Google (which is now my new online calculator when I am in class, when I cannot use R), if the power is a fraction (or to be more specific the inverse of an integer), then it works as Excel


you get

 But if you type (which should be close, from a continuity property of the power function)


you get

and similarly

On Wolfram Mathworld, enter

Mathematica does recognize that we try to deal with unit roots: the result is here

with – as expected – a numerical approximation

With Matlab, Mathieu did obtain the same as Mathematica (its decimal approximation). And to conclude, with R, Mathieu did obtain

> (-8)^(1/3)
[1] NaN
> (-8)^(.333333333333333)
[1] NaN

So for R, you cannot use this hat function on negative numbers.

Now, how can we interpret those outputs ?

1) My understanding is that clearly, with MS Excel,^{ab}\neq%20\left(x^a\right)^bsince^{\frac{2}{3}}\neq%20\left((-8)^{\frac{1}{3}}\right)^2

which is problematic. For instance, in insurance, with monthly discounts, we do have functions like^{\frac{k}{12}}. What if^{\frac{k}{12}}\neq%20\left(u^{\frac{1}{12}}\right)^k

2) The problem comes – probably (MS Excel is not an open software, so it might be hard to check) –  from the fact that^{\frac{1}{n}} is interpreted as an inverse of a (possibly) bijective function. To be more specific,^{\frac{1}{n}} means that^n=y. When is an odd integer, then (in real-analysis) there is a unique inverse, and thus,^{\frac{1}{n}} is uniquely defined, since\mapsto%20x^n is a bijective\mathbb{R}\rightarrow\mathbb{R} function. This is what MS Excel (and Google) is doing:\mapsto%20x^3 is a bijective\mathbb{R}\rightarrow\mathbb{R} function, so^{\frac{1}{3}} means that we need to find the unique (real) value such that^3=-8. Thus, somehow, it makes sense to return

3) There is still a problem with Google, and Mathematica. That is fine to return unit roots in\mathbb{C}. But how comes there is only one value ? I mean, yes\sqrt{3}%20\%20i is a possible answer, since\sqrt{3}%20\%20i)^3=-8

but one can also observe that , and similarly,^3=-8 and\sqrt{3}%20\%20i)^3=-8

One can check with

With R, since we do not deal with power function here, but with roots, if we want to find such that^3=-8, the function is

> polyroot(c(8,0,0,1))
[1]  1+1.732051i -2+0.000000i  1-1.732051i

Which is different… Weird isn’t it ?

7 thoughts on “Rationality, and MS Excel (and other calculators)”

  1. Some papers deal about this kind of problem. It has been used to fix them in more recent version. For instance, a certain time ago, MS Excel used the Koenig-Huyghens formula to compute the variance. If values are too large, you have the difference of two large quantities and troubles may appear! This has been changed in more recent version.

    1. Funny, that’s exactly the story I told Mathieu: a few years ago, there was no way to know what Excel actually did compute when you did ask for the “variance”. You had to compute it on a small sample, to see if it was divided by n or n-1…

  2. There’s no problem with Mathematica, it returns the value of exp(x log(y)) using a coherent definition of log.

  3. Excel is funny;
    it seems to accept that (-1)^(1/q) = -1 when q is odd (even if you write 1/q as an exact decimal representation, e.g. (-1)^0.04 = -1), but it doesn’t look like it will accept any other fractional exponent…
    My guess is after coding to check if x = 1/q in (-y)^x, they decided it wasn’t worth the trouble going further.
    A good example of : if you’re not gonna do it well…don’t do it at all !

Leave a Reply

Your email address will not be published. Required fields are marked *