Numbers is intended to be an intuitive alternative to Microsoft Excel and other popular spreadsheets, but as a calculation program it is perhaps not much to expect it to perform calculations properly. However, there are instances where the program may incorrectly calculate values you enter. One of these is if you attempt an odd-root (e.g., the cube root) of a negative number, where there is a mathematical answer, but which causes Numbers to give an error when attempted.
As an example, if you use Numbers (the current version being 3.5.2), then open it and enter the following calculation in a cell (copy and paste it):
=(-8)^(1/3)
This problem also occurs with other methods of calculating roots, including the built-in “POW” function for raising a value to a power, and then using either 1/3 or 0.333333333 as the power.
Of course taking odd roots of values may be relatively less popular than other evaluations in Numbers (especially for basic statistics and number manipulations); however, there may be times when it is necessary, and you find yourself in a rut and wondering how such a basic error could have made its way into a number-crunching application.
This error indicates Numbers cannot evaluate the formula, even though it is mathematically correct.
If you need to evaluate an odd root of a number that may be negative, then you can still do it in Numbers, but for now you will be required to use a conditional operation to determine if the input value is negative or positive, then compute the root on the absolute value of the input, followed by multiplying the result by -1 if the input value was negative.
The following function will do this for the cube root of a cell’s value (in this case, the cell being “A1”), so you can copy and paste this function and then change the input cell and root value accordingly (for those out of practice, a number to the 1/3 power computes the cube root, 1/2 power computes the square root, 1/5 power will compute the 5th root, etc.):
=IF(A1<0,-1*ABS(A1)^(1/3),A1^(1/3))
Hopefully Apple will address this and perhaps any similar problems in Numbers, as they undoubtedly cast doubt on the abilities of an otherwise impressive and useful spreadsheet program. This error happens on both the Mac OS and iOS versions of Numbers.
Special thanks to MacIssues reader Sergio for writing in about this issue.
It turns out that Spotlight has the same problem — compounded, in my case, by keyboard shortcuts quickly rewriting the three-character fraction to a single character, making Spotlight think it’s not seeing an expression at all.
For my purposes ( use ) Numbers is not at all intuitive. Also who wants to work thru the problem you cite ? So i broke down ( in tears one day ) and bot Excel. It has far more features and very much intuitive plus i can share/open Excel documents.
Apple wasted it’s time by doing a “Number” on it’s users.
“Numbers” can read Excel files and export to that format. But yes, I agree: for most uses Excel is a better program. The star of iWork is really Keynote.
Well Apple didn’t waste their time as most people don’t need the Office suite.
However, Apple needs to fix the Numbers issue cited above ASAP, otherwise the credibility of the calc’s could come into question.
For all my love of Apple… I still keep using Microsoft Office for Mac.
If you want “intuitive” you should use VisiCalc running in an Apple][ virtual, 8-bit machine! :+)
I think it can be argued that this is not a bug. The problem is that a negative number to some power only makes sense if the exponent is a rational number with an odd denominator. That seems to be the case here, but consider: 1/3 cannot be exactly represented in binary without using infinitely many bits. In fact, only rational numbers whose denominators are powers of 2 can be so written.
On the other hand, Excel does handle (-8)^(1/3), with the expected result -2. How do they do it? Either they must do it symbolically, bypassing the actual computation of the exponent 1/3, or they do some magic to determine that the exponent does indeed have this form.
It seems to be the latter. =(-8)^(0.333333333333333) in Excel works fine, but if I chop off just one decimal and write =(-8)^(0.33333333333333) instead, I get an error message. So presumably, when Excel sees a negative number to some power, it tries to determine if the exponent is sufficiently close to a rational number with an odd denominator, or not, and acts accordingly. But it is far from perfect: =(-8)^(2/3) fails (the correct answer would be 4).
What these programs should do is to provide a ROOT function with two arguments, so that ROOT(x,y) is equivalent to x^(1/y), except that it always does the right thing when x is negative and y is an integer. In the absence of such a function, a better work around is:
=SIGN(A1)*ABS(A1)^(1/3)
I was going to post, but I see that Harald has accurately and lucidly explained the difficulty. Even PCalc, my favourite RPN calculator on both Mac and iOS, produces an error with (-8)^1/3, for the reasons Harald has given (tantamount to “because binary math”). A symbolic calculation or a specific ROOT() function are good (but nonexistent in Numbers) solutions.
BTW, the current version of Numbers for Mac has a SIGN function, which cleans up Topher’s workaround some by getting rid of the IF():
=SIGN(A1)*A1^(1/3))
Just be sure that the fraction (e.g. 1/3, 1/5, etc) really is the inverse of an odd integer, because if it’s even (e.g. 1/2, 1/4, etc), either workaround generates an incorrect answer (should be complex or imaginary result, not a Real).
Correction:
=SIGN(A1)*ABS(A1)^(1/3))
Topher, please cancel my two responses, my apologies. I didn’t read quite to the end of Harald’s response, where he gives the same simplified workaround.
Scott, that’s okay with me. Thanks for supporting my analysis.