By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,377 Members | 1,722 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
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 gives
you 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.