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!!
solarissfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StephenJRCommented:
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))
0
solarissfAuthor Commented:
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?
0
StephenJRCommented:
Give it a try and see

MsgBox Application.StDev(Application.Index(v, Evaluate("Row(5:10)"), 2))
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

solarissfAuthor Commented:
I get type mismatch
0
StephenJRCommented:
How did you declare your array? Can you post a sample workbook?
0
solarissfAuthor Commented:
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

0
StephenJRCommented:
Not sure, a sample workbook would help. A quick test for me worked.
0
solarissfAuthor Commented:
the data is from tab "CDATA"
peer.xlsm
0
StephenJRCommented:
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

0
StephenJRCommented:
Btw, didn't see your workbook so have not tested on that.
0
solarissfAuthor Commented:
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
0
StephenJRCommented:
Yes, if you define a and b (or whatever) you can use this:

MsgBox Application.StDev(Application.Index(stddevArray, Evaluate("Row(" & a & ":" & b & ")"), 2))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
solarissfAuthor Commented:
awesome thanks!!! you guys make me look like a genius!!!
ha
0
StephenJRCommented:
Remember my cut if you get a pay rise. I know where you live ...
0
solarissfAuthor Commented:
DONE!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.