^ Operator - raising a negative to a non integer power

[Yornoc]

Hello all,

I need to calculate number^(exponent) where number is negative.

Excel allows me to do this on a worksheet.

However, when I try to do it in VBA, it does not seem possible. The help for ^Operator tells me that "Number can be negative only if exponent is an integer value".

Does anyone know of a way around this?

Any help or comment would be warmly welcomed.

Cheers,
Yornoc

 

[combo]

Guess that this does not mean Integer data type, this works:

Dim p As Double
p = 2
MsgBox (-1.3) ^ p

combo

 

[voodoojon]

Your original question is confusing, perhaps you think integers are only positive values?

integers = {....-3,-2,-1,0,1,2,3...}

The reason for this restriction:

"Number can be negative only if exponent is an integer value"

Is that a non integer power of a negative number can never be a real number, so it can't be stored in vba or excel unless you add a data type that stores complex numbers.

Jon

One day I will find a signature worthy of this space. That day has not yet come.

 

[Yornoc]

Thanks for your comments combo and voodoojon,

I apologise. My original question was confusing.

What I meant to say and didn't was that I am wanting to use a non integer exponent:

My requirement is to calculate something like (-0.4)^(1/3).

The answer is real. Excel (worksheet) calculated it as -0.73681. VBA is not as helpful...

Yornoc

 

[jges]

(-0.4)^(1/3) would yield a complex result.

What Excel gave you was -(0.4)^(1/3).

 

[voodoojon]

I now realise

a negative number can never be a real number, so it can't be stored in vba or excel unless you add a data type that stores complex numbers.

was wrong as proved by yornoc.

to add to the last two posts, (-0.4)^(1/3) yields two complex solutions and one real solution.

yonroc, if you're just looking for the real cube root of a negative number as in your example, you can use jges' method. ie
-p^(1/3) = -(p^(1/3))

This will not work for all noninteger powers though.

Jon

One day I will find a signature worthy of this space. That day has not yet come.

 

[jges]

This site may be of some interest to you:

http://mathworld.wolfram.com/CubeRoot.html

To turn your problem on its head you have
x^(1/n)=z
where x and n are known
rewritten
z^n=x
now you have a polynomial equation that you can find roots. There are a number of algorithms for finding roots (search the mathworld site given above for root finding or root extraction an it should have a number of links).

I think there are analytical solutions for equations up to degree 5, anything higher will require an iterative technique. There are a number of root finding algorithms, each having its strengths and weaknesses.

 

[Yornoc]

...and there i was thinking i knew all there was to know about cube roots!

really appreciate your time. thanks again, jges, Jon.

yornoc

 

[jges]

I dug around my numerical routines and found a Bisection rotine for finding roots of equations. After a few slight modifications it now finds the nth root of a given number.

I had not thought of using it for that purpose until I read this thread (I didn't know about the "number can only be negative only if exponent is an integer value" limitation of the ^ operator).

Here is some code if anyone is interested.

Option Explicit
Dim radicand As Double
Dim index As Double
Dim nthroot As Double

'---------------------------------------

Private Function f(ByVal radicand As Double, ByVal index As Long, ByVal nthroot As Double) As Double

  'x^(1/n) = z
  'x = radicand
  'n = index
  'z = nthroot
  'z^n - x = 0
  f = nthroot ^ index - radicand

End Function

'---------------------------------------

Private Function Bisection(ByVal LowerBound As Double, _
                          ByVal UpperBound As Double, _
                          Optional ByVal AllowableError As Double = 0.0000000001, _
                          Optional ByVal MaxIterations As Long = 10000) As Double

  'Bisection is a root finding algorithm, given a function and a range
  'that the root lies in (root must be between upper and lower bounds),
  'bisection will keep cutting the range in half until it finds the root
  '(or more accurately an approximation that lies within your error
  'tolerance)
  'Pros: 1) if a root exists in the range given, bisection will find it!
  'Cons: 1) if multiple roots exist in the range given, bisection may
  '         only find one of them (if there are an even number of roots
  '         in the range, bisection may fail completely)
  '      2) convergence may take more iterations than in other algorithms
  '         (though in other algorithms convergence may not be guaranteed)

  'This particular variation has been written to find the nth root of
  'a given number. Bisection will return 0 if it cannot find a root
  '(this happens when the result would be complex, i.e. trying to find
  'an even root of a negative number).

  Dim iteration As Long
  Dim error As Double
  Dim xroot As Double
  Dim test As Double
  
  'error = 2 * AllowableError
  iteration = 0
  
  Do
    xroot = (LowerBound + UpperBound) / 2
    iteration = iteration + 1
    'if the function = 0 we have found the root (exactly)
    error = f(radicand, index, xroot)
    'look for a sign change between UpperBound and xroot
    'if there is a sign change then the root is between
    'xroot and upperbound, otherwise the root is between
    'lowerbound and xroot
    test = f(radicand, index, UpperBound) * f(radicand, index, xroot)
    If test = 0 Then
      'we found the root exactly!
      'not likely to ever get here due to computer representation of
      'numbers and rounding errors, but it is a possibility
      error = 0
      Exit Do
    Else
      If test < 0 Then
        LowerBound = xroot
      Else
        UpperBound = xroot
      End If
    End If
  Loop Until (Abs(error) < AllowableError) Or (iteration > MaxIterations)

  If error > AllowableError Then
    'we must have run out of iterations
    'probably no root on given range
    Bisection = 0
  Else
    Bisection = xroot
  End If

End Function

'---------------------------------------

Private Sub cmdCalculate_Click()

  radicand = Val(txtRadicand.Text)
  index = Val(txtIndex.Text)
  
  If Abs(radicand) > 1 Then
    nthroot = Bisection(-radicand, radicand)
  Else
    nthroot = Bisection(-1, 1)
  End If
  
  lblNthRoot = nthroot
  lblCheckResult = nthroot ^ index
  
End Sub

For testing I put the code in a form with a command button (the last sub), 2 text boxes for input, and 2 labels for output.

x^(1/n)
x = radicand
n = index

 

[TriRyche]

[COLOR=red yellow] WOW! [/color]
One of the best threads I read in a while!
I'm in the process of self-learn C++ and VBA.
I'm outta my league!!