[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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!!
0
solarissf
Asked:
solarissf
  • 8
  • 7
1 Solution
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now