asked on

Calculate Standard Deviation of portion of Array

Hi Guys,

I have a 3 column array that say is around 100 elements long.
I'm trying to using the worksheet.function to calculate the STDDEV, but only of a piece of the array.

FYI, doing all this in VBA

Like

'stddevArray(3, a) = application.WorksheetFunction.StDev("item1 in array:item2 in array")

I understand the parameter has to be the range of stddev. Anyone know the quick and easy code for starting point and ending point in the array?

Thanks!!

StephenJR

Could you clarify please. You have an array 100 x 3 and you want to calculate the st dev of which bit?

e.g. this would give you stdev of 4th to 6th element in first column (where v is array):

MsgBox Application.StDev(Application.Index(v, Evaluate("Row(4:6)"), 1))

Stephen Forero

ASKER

array would be 3 columns by 100 rows in VBA
say I want the standard deviation of 2nd column, 5th row through 10th row.

will your formula above handle that?

StephenJR

Give it a try and see

MsgBox Application.StDev(Application.Index(v, Evaluate("Row(5:10)"), 2))

Stephen Forero

ASKER

I get type mismatch

StephenJR

How did you declare your array? Can you post a sample workbook?

Stephen Forero

ASKER

the array is made from the following code attached:

the data is

dec-09 -3.54%
jan-10 -11.72
feb-10 7.45
and so on.

i tried this
MsgBox Application.StDev(Application.Index(stddevArray, Evaluate("Row(5:10)"), 2))

Sub RollingSD()
Dim stddevArray() As Variant
Dim lastRowData As Integer
Dim a As Integer            'rows of Vami
Dim x As Integer            'rows of original data

a = 1
    Sheets("CData").Activate
    lastRowData = Range("B65536").End(xlUp).row
    ReDim stddevArray(1 To 3, 1 To a)
        For x = 2 To lastRowData
            ReDim Preserve stddevArray(1 To 3, 1 To a)
            stddevArray(1, a) = Cells(x, 1).Value
            stddevArray(2, a) = Cells(x, 2).Value
            a = a + 1
        Next x

Open in new window

StephenJR

Not sure, a sample workbook would help. A quick test for me worked.

Stephen Forero

ASKER

the data is from tab "CDATA"
peer.xlsm

StephenJR

OK, your array was the wrong way round, but in any case you can populate your array more quickly this way. Worked for me on a mock-up:

Sub RollingSD()

Dim stddevArray
Dim lastRowData As Integer
Dim a As Integer            'rows of Vami
Dim x As Integer            'rows of original data

Sheets("CData").Activate
stddevArray = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
MsgBox Application.StDev(Application.Index(stddevArray, Evaluate("Row(5:10)"), 2))

End Sub

Open in new window

StephenJR

Btw, didn't see your workbook so have not tested on that.

Stephen Forero

ASKER

Thank you so much Stephen. It does work... however I have one new problem with this.

the row to evaluate will be variable, so the section
Evaluate("Row(5:10)")

needs to be like Evaluate("Row(x:t)")

suggestions... and again I'm sorry for not specifying this earlier

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Stephen Forero

ASKER

awesome thanks!!! you guys make me look like a genius!!!
ha

StephenJR

Remember my cut if you get a pay rise. I know where you live ...

Stephen Forero

ASKER

DONE!