0

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. =1/3
  2. Copy/Paste Values to get the value 0.333333333333333
  3. =1/0.333333333333333
  4. 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.

4
  • 3
    I don't see the practical problem in your question, Excel is a spreadsheet application, not an advanced mathematical tool, we know it's limitations and around the limits it's not always predictable. About your specific example: 15 significant digit is valid for display only, probably for calculations it uses slightly more digits; however not being the one who programmed Excel I can't tell it for sure. May 10, 2017 at 18:53
  • 4
    1000^(1/3) is the same as expressing the cube root of 1000, which is 10. Excel is probably honoring the rules of fractional exponents and not actually doing any calculation when interpreting 1/3 in the exponent.
    – BrianC
    May 10, 2017 at 19:04
  • I haven't changed a single word of the original question. I have only added updates below it. May 10, 2017 at 22:30
  • @GregLovern Go read What Every Computer Scientist Should Know About Floating-Point Arithmetic and then come back when you have a real answerable question.
    – DavidPostill
    May 10, 2017 at 22:34

1 Answer 1

4

What is Excel doing to get exactly 10 out of that?

Excel is performing the same calculation, that is performed when POWER(Value, Exponent) is used, in order to calculate your =number^exponent equation

This can be confirmed by doing

  • `=1000^(1/3) which is 10
  • =1000000^(1/3) which is 100
  • =1000000^(1/2) which is 1000
  • =100^(1/2) which is 10.

Which matches

  • =POWER(1000,1/3) = 10
  • =POWER(1000000,1/3) = 100
  • =POWER(1000000,1/2) = 1000
  • =POWER(100,1/2) = 10

I took random exponents, 1/5 and 5, and verified the behavior also.

  • =10^(1/5) and =POWER(10,1/5) both come out to 1.584893192

  • =10^5 and=POWER(10,5)both come out to100000`

I appreciate BriainC's assistance in pointing me in the correct direction.

1000^(1/3) is the same as expressing the cube root of 1000, which is 10. Excel is probably honoring the rules of fractional exponents and not actually doing any calculation when interpreting 1/3 in the exponent.

It is worth pointing out that raising any value, to an exponent of 1/3, is how you calculate the cube root of that number in Excel. Raising any value, to an exponent of 1/2, is how you calculate the square root of that number in Excel. You can also use the =SQRT(Value) equation, but that gets you identical results as =POWER(Value,Exponent)

Excel Cube Root: The cube root of x is x^(1/3). The cube root of A1 is: =A1^(1/3). You can also use the built-in worksheet function POWER: =POWER(A1;1/3). To calculate Cube root in excel follow the below steps: Cube root formula =POWER(E3,1/3). Let’s check this formula. Cube root of 125.

Cube root function in Excel

enter image description here

I took 10 numbers between 2 and 16 and calculated all 4 combinations. I added the SQRT column to that SQRT(2) == POWER(2,1/2) == 2^(1/2) are equivalent Excel functions.

I can understand that POWER would detect .... But how does it know to do that without special-casing millions of possibilities?

It can treat every case exactly the same. It is uses the came code to calculate any value raised to any exponent.

Okay, but how does it know what input numbers to do that for? By special-casing millions of possibilities? Or would it more likely be following some rule or set of rules?

It is uses the came code to calculate any value raised to any exponent.

What is POWER doing to seemingly treat 1/3 as a true fraction? Excel sends operands to the CPU to do its math in binary, and surely we're not saying the CPU is doing the math with true fractions in base 10?!?!?

Base 2 and Base 10 is used to help us humans. A CPU does everything as binary. How any exponent calculation is converted into x86 is well defined.

I don't think the CPU has the ability to do calculations with the base-10 fraction 1/3, but if I'm wrong I'd be very interested in being pointed to information about that.

The calculator I used 20 years ago is able to perform exponent calculations with fractions. I will be unable to tell you what Excel does exactly to make that calculations. I can tell you that it would be eventually, be converted into an assembly calculation, which would be well defined behavior.

Ramhound's answer makes the point that Excel converts the power operator ("^") to the Power function

It actually is the other way around. A power function would be converted into a standard exponent calculations

1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – DavidPostill
    May 10, 2017 at 21:51

Not the answer you're looking for? Browse other questions tagged .