Standard deviation of a subset in VBA

[SHW2022]

Hello,

I am trying to create a function in VBA and I seem to be a bit stuck. I have created 5 subsets an they all work fine. What I then want to do is calculate the standard deviation of my subsets and take the average standard deviation. But my formula for standard deviation (in red) seems to not produce any results. I have tried searching and cannot find a solution anywhere. I have checked that my subsets contains only numerical values. Code detailed below:. Have i defined something incorrectly?

Function FITVol(rng As Range) As Double
Dim subsets(1 To 5) As Variant
Dim subsetStDev(1 To 5) As Double
Dim sumStDev As Double
Dim lastRow As Long, i As Long, j As Long

' Find the last row with data in the range
lastRow = rng.Rows.Count
Debug.Print "Last Row: " & lastRow

' Loop through the data, calculate subsets, and store the natural log of the ratio in subsets array
For i = 1 To 5
For j = i + 5 To lastRow Step 5
subsets(i) = subsets(i) & WorksheetFunction.Ln(rng.Cells(j, 1).Value / rng.Cells(j - 5, 1).Value) & ","
Next j
' Remove the trailing comma and split the string into an array
subsets(i) = Split(Left(subsets(i), Len(subsets(i)) - 1), ",")
Debug.Print "Subset " & i & ": " & Join(subsets(i), ",")
Next i

'Calculate standard deviation for each subset and store in subsetStDev array

For i = 1 To 5

subsetStDev(i) = WorksheetFunction.StDev(subsets(i))

' Output standard deviation to Immediate Window for debugging
Debug.Print "Subset " & i & " Standard Deviation: " & subsetStDev(i)
Next i

' Calculate the average of standard deviations
For i = 1 To 5
sumStDev = sumStDev + subsetStDev(i)
Next i

' Calculate the final result (average of standard deviations)
FITVol = sumStDev / 5
Debug.Print "Average of Standard Deviations: " & FITVol
End Function

To confirm, here is the Debug.Print of my subsets:

Subset 1: -1.40268043784976E-02,6.42673935161397E-02,-0.030939031938565,-2.06996998946987E-02,-5.59296897453847E-02,-3.29835990824823E-02,5.05237075458735E-02,-2.34609143908246E-02
Subset 2: 1.96408478699902E-02,4.08259753474539E-02,-3.52824173944672E-02,-3.37172783773844E-02,-4.67909355838842E-02,8.93980019460165E-03,-5.94079251496369E-03,0.014801380302262
Subset 3: 7.00126242265415E-03,5.57446037672143E-03,1.65253397538366E-02,-0.024891558388088,-6.35994296943924E-02,5.94079251496354E-03,-5.94079251496369E-03,2.35990690296357E-02
Subset 4: 1.24711654861162E-02,1.91048585927042E-02,-3.57678825722095E-02,-1.98075017901463E-02,-6.33858776030418E-02,2.10748320866318E-02,2.21181866417995E-02,0
Subset 5: 6.55952170294352E-02,-4.87921952056971E-02,-3.38932174496871E-02,-5.31116971661447E-02,-1.52689037915363E-02,3.62489625936878E-02,2.34609143908245E-02

 

[StephenCrump]

It looks like the problem is text, rather than numeric, values being passed to the StDev function.

Rather than concatenating and splitting, you could do this more directly, perhaps along these lines:

Function FITVol(rng As Range) As Double
    
    Dim subset() As Double
    Dim sumStDev As Double
    Dim N As Long, K As Long, i As Long, j As Long
    
    N = 5
    K = rng.Rows.Count / N - 1
    ReDim subset(1 To K)
    
    For i = 1 To N
        For j = 1 To K
            subset(j) = Log(rng(j * N + i, 1).Value / rng((j - 1) * N + i, 1).Value)
        Next j
        sumStDev = sumStDev + WorksheetFunction.StDev(subset)
    Next i
            
    FITVol = sumStDev / N

End Function

Sample output comparing VBA output and excel formulae. I don't know whether this is what you intend?

	A	B	C	D	E	F
1	Subset	Value	Calc	StDev	Average	VBA
2	1	2.11		0.1452	0.6088	0.6088
3	2	1.62		0.5297
4	3	1.1		0.6860
5	4	1.11		0.7594
6	5	1.41		0.9235
7	1	1.43	-0.3890
8	2	1.48	-0.0904
9	3	2.26	0.7201
10	4	1.01	-0.0944
11	5	2.36	0.5151
12	1	1.19	-0.1837
13	2	2.86	0.6588
14	3	1.76	-0.2501
15	4	2.69	0.9796
16	5	1.07	-0.7910
Sheet1

Cell Formulas
Range	Formula
E2	E2	=AVERAGE(D2:D6)
F2	F2	=FITVol(B2:B16)
D2:D6	D2	=STDEV(C7,C12)
C7:C16	C7	=LN(B7/B2)

 

[SHW2022]

Thank you so very much for your help this looks exactly like what I am after. I'm not back at my computer now until Monday morning so will confirm for certain then.

 

[RoryA]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Standard deviation of a subset in VBA

Hello, I am trying to create a function in VBA and I seem to be a bit stuck. I have created 5 subsets an they all work fine. What I then want to do is calculate the standard deviation of my subsets and take the average standard deviation. But my formula for standard deviation (in red) seems...

www.vbaexpress.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[SHW2022]

A reminder:

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Standard deviation of a subset in VBA

Hello, I am trying to create a function in VBA and I seem to be a bit stuck. I have created 5 subsets an they all work fine. What I then want to do is calculate the standard deviation of my subsets and take the average standard deviation. But my formula for standard deviation (in red) seems...

www.vbaexpress.com

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Apologies for not providing the link for where I had alternatively posted, I shall be sure to do this in the future.

 

[SHW2022]

It looks like the problem is text, rather than numeric, values being passed to the StDev function.

Rather than concatenating and splitting, you could do this more directly, perhaps along these lines:

Function FITVol(rng As Range) As Double
   
    Dim subset() As Double
    Dim sumStDev As Double
    Dim N As Long, K As Long, i As Long, j As Long
   
    N = 5
    K = rng.Rows.Count / N - 1
    ReDim subset(1 To K)
   
    For i = 1 To N
        For j = 1 To K
            subset(j) = Log(rng(j * N + i, 1).Value / rng((j - 1) * N + i, 1).Value)
        Next j
        sumStDev = sumStDev + WorksheetFunction.StDev(subset)
    Next i
           
    FITVol = sumStDev / N

End Function

Sample output comparing VBA output and excel formulae. I don't know whether this is what you intend?

	A	B	C	D	E	F
1	Subset	Value	Calc	StDev	Average	VBA
2	1	2.11		0.1452	0.6088	0.6088
3	2	1.62		0.5297
4	3	1.1		0.6860
5	4	1.11		0.7594
6	5	1.41		0.9235
7	1	1.43	-0.3890
8	2	1.48	-0.0904
9	3	2.26	0.7201
10	4	1.01	-0.0944
11	5	2.36	0.5151
12	1	1.19	-0.1837
13	2	2.86	0.6588
14	3	1.76	-0.2501
15	4	2.69	0.9796
16	5	1.07	-0.7910
Sheet1

Cell Formulas
Range	Formula
E2	E2	=AVERAGE(D2:D6)
F2	F2	=FITVol(B2:B16)
D2:D6	D2	=STDEV(C7,C12)
C7:C16	C7	=LN(B7/B2)

Thank you so much for this , the code you have provided seems to do the job - I'm just trying to break it all down so that I can replicate myself in the future if you dont mind? If K is non-integer does it convert to an integer by rounding up or down? I think it looks like it just takes the absolute value, so down?
I seem to be having a problem when my row count is 523 so K would be 130.75 (130?). I can't see why that would be a problem though. If I use the exact same data set with more or less rows it still works so I don't think the problem is in the data itself.

I also have found that I have some "N/A" in my dataset so I'll need to have a play around with how to skip that subset entry if it is an error.

 

[StephenCrump]

If K is non-integer does it convert to an integer by rounding up or down? I think it looks like it just takes the absolute value, so down?

K will round to the nearest integer, which will not always be what we want.

K = rng.Rows.Count / N - 1

But if your subsets can be different sizes, and there is also the possibility of #N/A errors, we'll need to loop through the subsets rather than just applying STDDEV to an array.

For the hypothetical example provided below, do the outputs shown meet your requirements?

	A	B	C	D	E
1	Subset	Value	Calc	StDev	Average
2	1	2.11		0.3411	0.6470
3	2	1.62		0.5249
4	3	1.10		0.6860
5	4	1.11		0.7594
6	5	1.41		0.9235
7	1	1.43	-0.3890
8	2	1.48	-0.0904
9	3	2.26	0.7201
10	4	1.01	-0.0944
11	5	2.36	0.5151
12	1	#N/A	#N/A
13	2	2.86	0.6588
14	3	1.76	-0.2501
15	4	2.69	0.9796
16	5	1.07	-0.7910
17	1	1.57	0.0934	<-- adj for #N/A
18	2	2.01	-0.3527
Sheet1

Cell Formulas
Range	Formula
E2	E2	=AVERAGE(D2:D6)
D2:D6	D2	=STDEV(FILTER(C$7:C$18,(A$7:A$18=A2)*ISNUMBER(C$7:C$18)))
B12	B12	=#N/A
C7:C16,C18	C7	=LN(B7/B2)
C17	C17	=LN(B17/B7)

 

[SHW2022]

So in my data sets I may have N/A starting in B2 and then continuing until values are available. Then once these are available I won' have any N/As from this point.