Statistics on LogNormal/Monthly Arrays

[Factor3]

I'm given a set of monthly returns, which are of varying length. I want to take those monthly returns and convert them into Log Normal Returns in a column next to them (it helps with statistical validity).


Sub LN_MONTHLY()
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy Destination:=Selection.Offset(0, 1)
Selection.End(xlUp).Select
Selection.Offset(1, 1).Activate
ActiveCell.FormulaR1C1 = "=EXP(RC[-1])-1"
Selection.Copy
Range(Selection, Selection.End(xlDown)).PasteSpecial (xlPasteFormulas)
Calculate
End Sub

That's all fine and Dandy (the only reason I copied the first row was to paste the formula only to the last row of the first set of returns...I'm new to this, so I am sure there are about a gazillion ways to expedite, I just don't know them).

Now, I need to actually use this array of data to generate some statistical information. The problem is that I don't want to use an exact range (because the ranges will differ depending on the data), so I am trying to assign an "array" variable to it... like so (the first 8 lines of this code are the EXACT same as the one above, the new stuff starts in red)::banghead:

Sub_STORING()

Dim MoR() As Range, ComR() As Range
Dim MoMu as Double

Range(Selection, Selection.End(xlDown)).Select
Selection.Copy Destination:=Selection.Offset(0, 1)
Selection.End(xlUp).Select
Selection.Offset(1, 1).Activate
ActiveCell.FormulaR1C1 = "=EXP(RC[-1])-1"
Selection.Copy
Range(Selection, Selection.End(xlDown)).PasteSpecial (xlPasteFormulas)
Selection.Offset(1,1).Activate
ComR = Range(Selection, Selection.End(xlDown))
Range("B3").FormulaR1C1 = "=SQRT(VAR(ComR)*(253/365*12))"
End Sub

The (253/365*12) at the very end is actually what I want to be the "stored value" of variable "MoMu" (Dimmed Double at the top).

There are several other statistical tests that I want to do on this array (ComR) as well as the other array (MoR). These tests are one cell outputs (not array outputs) of the results, which makes me think it's not a difficult question that I am asking.

Thanks so much,

[Bob Phillips]

I am nit sure that I understand, but do you mean that you want to store the result of that formula in MoMu instaned of B3?

And the formula should be


Range("B3").Formula = "=SQRT(VAR(" & ComR.Address & ")*(253/365*12))"

[Factor3]

'All this section (very ineffectively) does is take a column of_
monthly returns, and put right next to it, a column of Log_
Based returns (the formula being EXP(N)-1)

Sub LN_MONTHLY()
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy Destination:=Selection.Offset(0, 1)
Selection.End(xlUp).Select
Selection.Offset(1, 1).Activate
ActiveCell.FormulaR1C1 = "=EXP(RC[-1])-1"
Selection.Copy
Range(Selection, Selection.End(xlDown)).PasteSpecial (xlPasteFormulas)
Calculate
End Sub


The next, and difficult part (only for me of course), is to then access those arrays for statistical analysis. So if I wasn't in VBA and was
just in Exce, it would look like:
=SQRT(VAR(B2:B256)*8.317). It's the Inside Address "B2:B256" that
I am trying to assign an array variable to (in the case below, it is ComR).


This way in the VBA Code I (hopefully) would be able to write:
Range("B3").FormulaR1C1 = "=SQRT(VAR(ComR)*(8.317))"
End Sub

Does that make it clearer (of course, there's a good chance that I'm missing a whole lot, and beyond help:)

Thanks so much,:friends: