459,377 Members | 1,722 Online Need help? Post your question and get tips & solutions from a community of 459,377 IT Pros & Developers. It's quick & easy.

# Runtime Error w/Exponentiation?

 P: n/a ----------------------------------------------------------------- Sub Sheesh() Dim myYears As Double Dim myRawCumulative As Double Dim myAnnualizedROR As Double myYears = 1.25 myRawCumulative = -9.24161581346505 myAnnualizedROR = ((myRawCumulative ^ (1 / myYears)) - 1) End Sub ----------------------------------------------------------------- In the Immediate window: Sheesh (code breaks at line 3: "Runtime error 5: Invalid procedure call or argument) But, also in the immediate window, ?((-9.24161581346505 ^ (1 / myYears)) - 1) -6.92377191354565 And then, just to be check: ?((myRawCumulative ^ (1 / myYears)) - 1) ("Runtime error 5: Invalid procedure call or argument) ---------------------------------------------------------------- Change the sign on myRawCumulative, and it works. Something to do with exponentiating a negative number? But why does it work with the explicit value in the Immediate window and not with the Double field? I'm at the end of my little string on this one. Nov 13 '05 #1
5 Replies

 P: n/a Per PeteCresswell:Sub Sheesh() Just to trim away some of the fat: -------------------------------------- Sub Oy() Dim myAnnualizedROR As Double Const myRawCumulative = -2 myAnnualizedROR = myRawCumulative ^ 0.8 End Sub -------------------------------------- Same thing. Abends w/RunTime 5, but the calc works on an Immediate line if the value -2 is supplied directly instead of via the constant. i.e. ?myRawCumulative ^ 0.8 ... Fails ?-2 ^ 0.8 ... Succeeds ALSO, it works via the constant if the constant's value is changed from a -2 to +2. -- PeteCresswell Nov 13 '05 #2

 P: n/a Per (Pete Cresswell):Sub Oy() Looks like I'm not the first one: http://www.excelforum.com/archive/in.../t-237761.html -- PeteCresswell Nov 13 '05 #3

 P: n/a Per (Pete Cresswell):Looks like I'm not the first one:http://www.excelforum.com/archive/in.../t-237761.html Can't figure out the Const vs literal number part, but VB's error 5 when attempting exponentiation of a negative number to a non-integral power seems tb a fact of life. 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 -------------------------------------- -- PeteCresswell Nov 13 '05 #4

 P: n/a (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. Nov 13 '05 #5

 P: n/a Per ji********@compumarc.com:If your idea givesyou the results you want then go for it. Therein lies the problem.... I don't have a clue... You're in the presence of somebody who, before Quicken came around, used to keep two checking accounts for use on alternate months because it make reconciling easier. One thing, though, computing rolling 10-year quarterly and monthly annualized returns is kicking the brains out of my PC.... The last test ran over 12 hours without getting through all the 8,000 funds/15 years of returns in question. Today I did some tuning and it seems to only take 1/3 the time for a small sample. But I'm starting to think it's wasteful for me to be crunching all these numbers in a big financial institution and using my own dubious methods to boot. I think my manhours would be better invested searching out databases that already have this stuff computed....and that's what I started doing today. -- PeteCresswell Nov 13 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion. 