I am trying to compute the cube root of a negative number in VBA for Excel. Taking the cube root of a negative number in an Excel worksheet works fine:
=(-2)^(1/3)
-1.25992
However the same concept coded into VBA:
(-2)^(1/3)
gives a #VALUE!
error when returning the value to the worksheet. I suspect VBA is parsing the 1/3
to its decimal representation 0.3333...
and therefore causing a mathematical error since negative numbers can't be rai开发者_JS百科sed to non-integer powers in the real domain. Is there a workaround?
Try using the POWER()
function:
POWER(-2, 1/3)
I think CarlF's/your answer is the best, but it's worth mentioning that you can use Application.Evaluate to return the result of a literal Excel formula:
Range("A1") = Application.Evaluate("=(-2)^(1/3)")
For me, it didn't work with (-2)^(1/3)
as you stated.
But it worked with -2^(1/3)
.
I have no idea why is this.
If x is a positive real number then the cube root of -x is simply the negation of the cube root of x, i.e., -(x^(1/3).
精彩评论