Calculate the Standard Deviations for every hour and then the Average of all Standard Deviations in VBA Excel

[mcm1978]

Dear Experts,

I have a table of measurements which is done for some days and minutes. I would like to calculate in VBA
the Standard Deviation per hour and then calculate the Average over all Standard Deviations. An example how this is done in excel is shown below. My VBA code is shown below and I am somehow stuck! I would appreciate every help .

Cheers,

Michael

Private objX() As Variant, objY() As Variant, objZ() As Variant, lngCount As Long
'Gets the Data and performs the calculations
Sub GetData()

Calc ("Data")

End Sub
Function Calc(strSheet As String) As Double

Dim dblSumAverage As Double
Dim dblAverage As Double
Dim dblSumStdDev As Double
Dim GetLastValue As Double
Dim varElement As Variant
Dim wsFnc As WorksheetFunction

Call prcDatenObjekt_erzeugen(strSheet:=strSheet)

For varElement = 1 To lngCount - 1
dblSumAverage = dblSumAverage + (objZ(varElement + 1) - objZ(varElement)) / objZ(varElement)
Next

dblAverage = dblSumAverage / (lngCount - 1)

For varElement = 1 To lngCount - 1
dblSumStdDev = dblSumStdDev + ((objZ(varElement + 1) - objZ(varElement)) / objZ(varElement) - dblAverage) ^ 2
Next

'StdDev in percent
dblSumStdDev = (dblSumStdDev / (lngCount - 2)) ^ 0.5

Erase objX, objY, objZ

End Function
Sub prcDatenObjekt_erzeugen(ByVal strSheet As String)

Dim arrX, arrY, arrZ, lngX As Long

With Sheets(strSheet)
arrX = .Cells(5, 1).Resize(Application.WorksheetFunction.Count(.Range(.Cells(5, 1), _
.Cells(Rows.Count, 1).End(xlUp))))
arrY = .Cells(5, 1).Resize(Application.Count(.Range(.Cells(5, 1), _
.Cells(Rows.Count, 1).End(xlUp)))).Offset(, 1)
arrZ = .Cells(5, 1).Resize(Application.Count(.Range(.Cells(5, 1), _
.Cells(Rows.Count, 1).End(xlUp)))).Offset(, 2)
End With

lngCount = 0

For lngX = LBound(arrX) To UBound(arrX)
lngCount = lngCount + 1
ReDim Preserve objX(0 To lngCount)
ReDim Preserve objY(0 To lngCount)
ReDim Preserve objZ(0 To lngCount)
objX(lngCount) = arrX(lngX, 1) * 1
objY(lngCount) = arrY(lngX, 1) * 1
objZ(lngCount) = arrZ(lngX, 1) * 1
Next lngX
End Sub

Date</SPAN>	Time</SPAN>	Measurement</SPAN>	Relative Deviation </SPAN>		Date</SPAN>	Time</SPAN>	Std. Dev. Matrix</SPAN>		Average Std. Dev.</SPAN>
04/02/12</SPAN>	8:00:00</SPAN>	1.33360 </SPAN>			02/04/2012</SPAN>	8</SPAN>	0.0137%</SPAN>		0.0172%</SPAN>
04/02/12</SPAN>	8:01:00</SPAN>	1.33390 </SPAN>	0.0002250</SPAN>		02/04/2012</SPAN>	9</SPAN>	0.0217%</SPAN>
04/02/12</SPAN>	8:02:00</SPAN>	1.33380 </SPAN>	-0.0000750</SPAN>		02/04/2012</SPAN>	10</SPAN>	0.0167%</SPAN>
04/02/12</SPAN>	8:03:00</SPAN>	1.33350 </SPAN>	-0.0002249</SPAN>		02/04/2012</SPAN>	11</SPAN>	0.0151%</SPAN>
04/02/12</SPAN>	8:04:00</SPAN>	1.33380 </SPAN>	0.0002250</SPAN>		02/04/2012</SPAN>	12</SPAN>	0.0122%</SPAN>
04/02/12</SPAN>	8:05:00</SPAN>	1.33380 </SPAN>	0.0000000</SPAN>		02/04/2012</SPAN>	13</SPAN>	0.0130%</SPAN>
04/02/12</SPAN>	8:06:00</SPAN>	1.33370 </SPAN>	-0.0000750</SPAN>		02/04/2012</SPAN>	14</SPAN>	0.0162%</SPAN>
04/02/12</SPAN>	8:07:00</SPAN>	1.33400 </SPAN>	0.0002249</SPAN>		02/04/2012</SPAN>	15</SPAN>	0.0168%</SPAN>
04/02/12</SPAN>	8:08:00</SPAN>	1.33430 </SPAN>	0.0002249</SPAN>		02/04/2012</SPAN>	16</SPAN>	0.0373%</SPAN>
04/02/12</SPAN>	8:09:00</SPAN>	1.33410 </SPAN>	-0.0001499</SPAN>		03/04/2012</SPAN>	8</SPAN>	0.0139%</SPAN>
04/02/12</SPAN>	8:10:00</SPAN>	1.33430 </SPAN>	0.0001499</SPAN>		03/04/2012</SPAN>	9</SPAN>	0.0172%</SPAN>
04/02/12</SPAN>	8:11:00</SPAN>	1.33430 </SPAN>	0.0000000</SPAN>		03/04/2012</SPAN>	10</SPAN>	0.0143%</SPAN>
04/02/12</SPAN>	8:12:00</SPAN>	1.33450 </SPAN>	0.0001499</SPAN>		03/04/2012</SPAN>	11</SPAN>	0.0177%</SPAN>
04/02/12</SPAN>	8:13:00</SPAN>	1.33460 </SPAN>	0.0000749</SPAN>		03/04/2012</SPAN>	12</SPAN>	0.0143%</SPAN>
04/02/12</SPAN>	8:14:00</SPAN>	1.33440 </SPAN>	-0.0001499</SPAN>		03/04/2012</SPAN>	13</SPAN>	0.0183%</SPAN>
04/02/12</SPAN>	8:15:00</SPAN>	1.33440 </SPAN>	0.0000000</SPAN>		03/04/2012</SPAN>	14</SPAN>	0.0169%</SPAN>
04/02/12</SPAN>	8:16:00</SPAN>	1.33430 </SPAN>	-0.0000749</SPAN>		03/04/2012</SPAN>	15</SPAN>	0.0139%</SPAN>
04/02/12</SPAN>	8:17:00</SPAN>	1.33410 </SPAN>	-0.0001499</SPAN>		03/04/2012</SPAN>	16</SPAN>	0.0195%</SPAN>

<TBODY>
</TBODY>