Why is Excel not calculating the cube root as the cube root?

user_7852849 0
Viewed 102

I have a suspicion Excel is calculating exponents that are repeating decimals represented as a fraction (e.g. 1/3) differently than ones that don't have repeating decimals (e.g. 1/2). It is preventing me for a formula that finds perfect cubes in a list of numbers.

Column A lists numbers 1 to 100.

Column B has the following formula (starting with row 5):

=IF($A5^(1/2)-ROUND($A5^(1/2),0)=0,1,0)

This should return "1" if the number in column A is a perfect square, like 1, 4, 9, etc. and does so correctly. This other formula I originally wrote also works: =IF(SQRT($A8)-ROUND(SQRT($A8),0)=0,1,0).

Column C has the following formula (starting with row 5):

=IF($A5^(1/3)-ROUND($A5^(1/3),0)=0,1,0)

Note that it is the exact same as the perfect square identifying formula, except there is a 3 where there was a 2. This is not returning a "1" for perfect cubes like 8, 27, 64. etc. (but does return a "1" for the number 1).

Can anyone help me correct this?

cube formula math excel
1 Answers

I apologize for not knowing how to pick a comment as an answer, so I aggregated the helpful comments and am posting them as an answer.

Main Explanation: See Floating-point arithmetic may give inaccurate results in Excel. If you examine the underlying xml, you will see that Excel is calculating A1^(1/3) as 1.9999999999999998. The article explains why, and suggests some work arounds. – Ron Rosenfeld

Workaround I picked: Can't you replace $A5^(1/3)-ROUND($A5^(1/3) with $A5 - ROUND($A5^(1/3))^3? – r3mainer

Thanks all!

user_78528490
Related