(Pete Cresswell) wrote:

Looks like the workaround is:

--------------------------------------

Function OhWell() As Double

Dim myAnnualizedROR As Double

Const myRawCumulative = -2

If myRawCumulative < 0 Then

myAnnualizedROR = -(Abs(myRawCumulative) ^ 0.8)

Else

myAnnualizedROR = myRawCumulative ^ 0.8

End If

OhWell = myAnnualizedROR

End Function

The answer you have is wrong in a certain sense but Microsoft never

claimed it was right :-).

result = number ^ exponent

number can be negative only if exponent is an integer value.

-- From ^ operator help (A97)

The ^ operator was never designed for the condition you encountered.

An alternative to a ^ b is Exp(b * log(a)). Note that the Log function

returns the natural logarithm. Also note that log(a) is not defined

for a <= 0 since there is no x that makes e ^ x non-positive.

Resorting to complex numbers, log(-a) = log(a) + n Pi i, n is any odd

integer. The choice of n is determined by what makes sense for the

problem you have (usually determinied by what's called a boundary

condition). So when a < 0, Exp(b * log(a)) = Exp(b * log(-a)) *

[Cos(b*n*Pi) + iSin(b*n*Pi)].

'Start Module Code-------

Type Complex

R As Double

I As Double

End Type

Public Function Exponentiate(a As Double, b As Double) As Complex

Const Pi = 3.14159265359 'Approximation

If a > 0 Then

Exponentiate.R = Exp(b * Log(a))

Exponentiate.I = 0

ElseIf a = 0 Then

'We'll let zero to any power be 0 by definition

Exponentiate.R = 0

Exponentiate.I = 0

Else

Exponentiate.R = Exp(b * Log(-a)) * Cos(b * Pi) 'If n turns out 1

Exponentiate.Z = Exp(b * Log(-a)) * Sin(b * Pi) 'If n turns out 1

End If

End Function

'End Module Code-------

would give a mathematically consistent value for a ^ b. I think you

can't get e ^ x to equal 0 + 0i even when x is allowed to be complex.

I hand-waved a little in substituting Exp(b * log(a)) for a ^ b in the

complex domain.

You could get the real part by using, for example:

Exponentiate(myRawCumulative, 1 / myYears).R

Your idea of Sgn(a) * (Abs(a) ^ b) sounds iffy but mathematical

consistency isn't always all it's cracked up to be. If your idea gives

you the results you want then go for it.

I hope this helps,

James A. Fortune

Disclaimer: My math books are at home.