use of worksheet function average in VBA
 

Hi,

I am trying to use the AVERAGE worksheetfunction in VBA, but don't
know how to ... the code below calculates a binomial stock price tree
for 10 time periods ... I now want to calculate the maximum stock
price across all states during a specific time period ... for example
for time period 10, I have 11 states. the maximum should now be
calculated for time period 10 across the 11 states (the problem is
therefore one of calculating the average of one dimension of a
multidimension array) ...

thanks in advance,

thei

Code:

Sub AverageStockPrice()

'input parameters
sig = 0.4
T = 1
N = 10
r = 0.05
S = 100

Dim St() As Double
Dim AvSt

'initialise parameters
dt = T / N
u = Exp(sig * Sqr(dt))
d = 1 / u
pu = (Exp(dt * r) - d) / (u - d)
pd = 1 - pu
edx = u / d
disc = Exp(-r * dt)

ReDim St(N, 0 To N)

St(0, 0) = S

'initialise asset prices
ReDim St(N, 0 To N)
St(0, 0) = S

For Index = 1 To N Step 1
St(Index, 0) = St(0, 0) * d ^ (Index - 0)
For State = 1 To Index
St(Index, State) = St(Index, State - 1) * edx
Next State
Next Index

'calculate average stock price across states at each time index

For Index = N To 0 Step -1
AvSt = Application.Average
Next Index

End Sub

use of worksheet function average in VBA
 

You can use the Application.Index

set the 2 or 3 argument to zero depending on what you want to slice. See
Excel help on the index function for an explanation.

--
Regards,
Tom Ogilvy

"Paul" wrote in message
om...
Hi,

I am trying to use the AVERAGE worksheetfunction in VBA, but don't
know how to ... the code below calculates a binomial stock price tree
for 10 time periods ... I now want to calculate the maximum stock
price across all states during a specific time period ... for example
for time period 10, I have 11 states. the maximum should now be
calculated for time period 10 across the 11 states (the problem is
therefore one of calculating the average of one dimension of a
multidimension array) ...

thanks in advance,

thei

Code:

Sub AverageStockPrice()

'input parameters
sig = 0.4
T = 1
N = 10
r = 0.05
S = 100

Dim St() As Double
Dim AvSt

'initialise parameters
dt = T / N
u = Exp(sig * Sqr(dt))
d = 1 / u
pu = (Exp(dt * r) - d) / (u - d)
pd = 1 - pu
edx = u / d
disc = Exp(-r * dt)

ReDim St(N, 0 To N)

St(0, 0) = S

'initialise asset prices
ReDim St(N, 0 To N)
St(0, 0) = S

For Index = 1 To N Step 1
St(Index, 0) = St(0, 0) * d ^ (Index - 0)
For State = 1 To Index
St(Index, State) = St(Index, State - 1) * edx
Next State
Next Index

'calculate average stock price across states at each time index

For Index = N To 0 Step -1
AvSt = Application.Average
Next Index

End Sub