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