Like other spreadsheets, Excel has 15 significant digits of precision. So this formula:
=1/3
Really returns this:
0.333333333333333
You can see that if you format the cell to show more than 15 decimal places.
So 1/3 in Excel gives us ALMOST 1/3, as close as we can get in 15 significant digits. "Significant digits" is the quantity of digits from the first nonzero digit through the last nonzero digit, regardless of where the decimal is.
So although 1000^(1/3) is 10, this formula:
=1000^(0.333333333333333)
Returns this:
9.99999999999998
Generally you get almost as many (sometimes just as many) nines as the number of threes you provide. So far this is all expected given the constraints of 15 significant digits of precision.
But if you enter this formula:
=1000^(1/3)
You get EXACTLY 10. Even if you change the number formatting to show the max possible 30 decimal places, and even if you select the formula and press F9 to evaluate it in the formula bar, it returns EXACTLY 10.
What is Excel doing to get exactly 10 out of that? The "1/3" in the formula isn't really exactly 1/3 by the time the power operator ("^") gets its hands on it; by then it's really 0.333333333333333.
UPDATE:
Okay, I think I know what's going on to allow Excel to get that exact result of 10, without doing any special-casing.
First, Excel would have an nth-root algorithm, and =1000^(1/3) would be sent through that algorithm to get the cube root of 1000.
(FWIW, a formula like =1000^(2/3) would be divided into two problems -- the square of 1000, and then the cube root of that result, or the cube root of 1000, and then the square of that result.)
According to Excel's normal rules of evaluation, the first thing that would happen is that 1/3 would be evaluated to 0.333333333333333. Then, that 0.333333333333333 would be inverted and sent to the nth root algorithm.
But what happens when Excel gets the inverse of 0.333333333333333? We get exactly 3, because 1/0.333333333333333 = 3.000000000000003, and with Excel's 15 significant digits of precision, 3.000000000000003 is truncated to 3.00000000000000, or exactly 3.
So Excel's nth-root algorithm is asked to get the cube root of 1000. Not the 3.000000000000003 root, but the exact cube root. And the answer to that of course is exactly 10.
I looked at what Excel does to the inverse of the inverse of all whole numbers through 150. I first got the inverse, then copy/pasted values to make sure Excel was doing doing any special casing on the inverse of an inverse, and then got the inverse of the value of the inverse. For example:
- =1/3
- Copy/Paste Values to get the value 0.333333333333333
- =1/0.333333333333333
- Result: exactly 3.
All of them came out to their exact original whole numbers. None came out to something like 37.9999999999999 or 38.0000000000001.
So that would seem to explain why =1000^(1/3) comes out as exactly 3. No special handling is needed. It's just that when Excel gets the inverse of 1/3 to get the cube root of 1000, it gets exactly 3. And the same is probably true of most or all similar examples.
On the other hand, that doesn't explain why =1000^(0.333333333333333) returns 9.99999999999998 instead of 10. That seems to contradict what we see Excel do with 1/0.333333333333333.