Problem
The following statistical functions were created to calculate statistics of experiments:
- Mean
- Peak to Peak
- Standard Deviation
- Variance
- Mean Absolute Error (MAE)
- Mean Square Error (MSE)
- Root Mean Square Error (RMSE)
So there are some statistics that needs an ideal point and some that doesn't.
The following image contains the data:
Where there are two columns, the first contains the measured value and the second the quantity that the data repeats.
Statistical UDFs
Each function will have as input only the first data column and the quantities must be on the right.
Mean
The function on G3 is =MeanArr(C2:C20)
And the code is:
Public Function MeanArr(rng As Range) As Double
Dim Arr()
Dim ws As Worksheet
Dim i As Long, j As Long
Set ws = Application.Caller.Parent
Dim cell As Range
With ws
For Each cell In rng
If cell.Offset(0, 1) > 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
For i = 0 To cell.Offset(0, 1) - 1
Arr(j + i) = cell
Next i
j = j + i
ElseIf cell.Offset(0, 1) = 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
i = 0
Arr(j + i) = cell
j = j + 1
End If
Next cell
'Mean
MeanArr= Application.WorksheetFunction.Average(Arr)
End With
Exit Function
ErrHandler:
MeanArr = "Error"
End Function
It is the Arithmetic Mean:
Peak to Peak
The function on G4 is =PeaktoPeak(C2:C20)
And the code is:
Public Function PeaktoPeak(rng As Range) As Double
Dim Arr()
Dim ws As Worksheet
Dim i As Long, j As Long
Set ws = Application.Caller.Parent
Dim cell As Range
With ws
For Each cell In rng
If cell.Offset(0, 1) > 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
For i = 0 To cell.Offset(0, 1) - 1
Arr(j + i) = cell
Next i
j = j + i
ElseIf cell.Offset(0, 1) = 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
i = 0
Arr(j + i) = cell
j = j + 1
End If
Next cell
'Peak to Peak
PeaktoPeak = WorksheetFunction.Max(Arr) - WorksheetFunction.Min(Arr)
End With
Exit Function
ErrHandler:
PeaktoPeak = "Error"
End Function
Peak to Peak is the amplitude of the data, it is the max minus the min.
Standard Deviation
The function on G5 is StdDeviation(C2:C20)
.
Public Function StdDeviation(rng As Range) As Double
Dim Arr()
Dim ws As Worksheet
Dim i As Long, j As Long
Set ws = Application.Caller.Parent
Dim cell As Range
With ws
For Each cell In rng
If cell.Offset(0, 1) > 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
For i = 0 To cell.Offset(0, 1) - 1
Arr(j + i) = cell
Next i
j = j + i
ElseIf cell.Offset(0, 1) = 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
i = 0
Arr(j + i) = cell
j = j + 1
End If
Next cell
'Standard Deviation
StdDeviation = WorksheetFunction.StDev(Arr)
End With
Exit Function
ErrHandler:
StdDeviation = "Error"
End Function
The standard deviation is a measure that is used to quantify the amount of variation or dispersion of a set of data values.
Variance
The function on G7 is =Variance(C2:C20)
Public Function Variance(rng As Range) As Double
Dim Arr()
Dim ws As Worksheet
Dim i As Long, j As Long
Set ws = Application.Caller.Parent
Dim cell As Range
With ws
For Each cell In rng
If cell.Offset(0, 1) > 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
For i = 0 To cell.Offset(0, 1) - 1
Arr(j + i) = cell
Next i
j = j + i
ElseIf cell.Offset(0, 1) = 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
i = 0
Arr(j + i) = cell
j = j + 1
End If
Next cell
'Var
Variance = WorksheetFunction.Var(Arr)
End With
Exit Function
ErrHandler:
Variance = "Error"
End Function
The Variance is the expectation of the squared deviation of a random variable from its mean. Informally, it measures how far a set of (random) numbers are spread out from their average value.
Mean Absolute Error (MAE)
The function on G6 is =MAE(C2:C20;B1)
Public Function MAE(rng As Range, ideal As Double) As Double
Dim Arr()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim Sum As Double
Set ws = Application.Caller.Parent
Dim cell As Range
With ws
For Each cell In rng
If cell.Offset(0, 1) > 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
For i = 0 To cell.Offset(0, 1) - 1
Arr(j + i) = cell
Next i
j = j + i
ElseIf cell.Offset(0, 1) = 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
i = 0
Arr(j + i) = cell
j = j + 1
End If
Next cell
'y=y1-t_ideal; %t_ideal is the square wave of ideal communication and y1 the test vector
For i = LBound(Arr) To UBound(Arr)
Arr(i) = Arr(i) - ideal
Next i
'%Absolute Value
For i = LBound(Arr) To UBound(Arr)
Arr(i) = Abs(Arr(i))
Next i
's=sum(se);
Sum = 0
For i = LBound(Arr) To UBound(Arr)
Sum = Sum + Arr(i)
Next i
'Mean Absolute Error
MAE = Sum / (UBound(Arr) + 1)
End With
Exit Function
ErrHandler:
MAE = "Error"
End Function
The Mean Absolute Error is a measure of difference between two continuous variables. Consider a scatter plot of n points, where point i has coordinates (xi, yi)... Mean Absolute Error (MAE) is the average vertical distance between each point and the identity line. MAE is also the average horizontal distance between each point and the identity line.
Calculated by the following formula:
Mean Squared Error (MSE)
The function on G2 is MSE(C2:C20;B1)
Public Function MSE(rng As Range, ideal As Double) As Double
Dim Arr()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim Sum As Double
Set ws = Application.Caller.Parent
Dim cell As Range
With ws
For Each cell In rng
If cell.Offset(0, 1) > 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
For i = 0 To cell.Offset(0, 1) - 1
Arr(j + i) = cell
Next i
j = j + i
ElseIf cell.Offset(0, 1) = 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
i = 0
Arr(j + i) = cell
j = j + 1
End If
Next cell
'y=y1-t_ideal; %t_ideal is the square wave of ideal communication and y1 the test vector
For i = LBound(Arr) To UBound(Arr)
Arr(i) = Arr(i) - ideal
Next i
'%Square Error, where .^ is used to square vector
For i = LBound(Arr) To UBound(Arr)
Arr(i) = Arr(i) ^ 2
Next i
's=sum(se);
Sum = 0
For i = LBound(Arr) To UBound(Arr)
Sum = Sum + Arr(i)
Next i
'mse=s/n; %Mean Square Error
MSE = Sum / (UBound(Arr) + 1)
End With
Exit Function
ErrHandler:
MSE = "Error"
End Function
The Mean Squared Error of an estimator (of a procedure for estimating an unobserved quantity) measures the average of the squares of the errors—that is, the average squared difference between the estimated values and what is estimated.
Formula:
Root Mean Square Deviation (RMSE)
The formula on G1 is =RMSE(C2:C20;B1)
Public Function RMSE(rng As Range, ideal As Double) As Double
Dim Arr()
Dim ws As Worksheet
Dim i As Long, j As Long
Dim Soma As Double, MSE As Double
Set ws = Application.Caller.Parent
Dim cell As Range
With ws
For Each cell In rng
If cell.Offset(0, 1) > 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
For i = 0 To cell.Offset(0, 1) - 1
Arr(j + i) = cell
Next i
j = j + i
ElseIf cell.Offset(0, 1) = 1 Then
ReDim Preserve Arr(cell.Offset(0, 1) + j - 1)
i = 0
Arr(j + i) = cell
j = j + 1
End If
Next cell
'y=y1-t_ideal; %t_ideal is the square wave of ideal communication and y1 the test vector
For i = LBound(Arr) To UBound(Arr)
Arr(i) = Arr(i) - ideal
Next i
'%Square Error, where .^ is used to square vector
For i = LBound(Arr) To UBound(Arr)
Arr(i) = Arr(i) ^ 2
Next i
's=sum(se);
Sum = 0
For i = LBound(Arr) To UBound(Arr)
Sum = Sum + Arr(i)
Next i
'mse=s/n; %Mean Square Error
MSE = Sum / (UBound(Arr) + 1)
'rmse=sqrt(mse) %Root Mean Square Error
RMSE = Sqr(MSE)
End With
Exit Function
ErrHandler:
RMSE = "Error"
End Function
Root Mean Square Deviation (RMSE) is a frequently used measure of the differences between values (sample or population values) predicted by a model or an estimator and the values observed.
Formula:
Questions
- How is the performance? Can it improve?
- Are the results ok? Are the functions working properly?
- How to make a proper
ErrHandler
? - Should i use
WorksheetFunction
or created my own UDFs? If the quantity of data gets really large. - I was thinking... Should I use a Global Array for each Sheet? So it doesn't have to calculate an array of data for each function again?
- Further tips/help are welcome. Or another improvements.
Just for reference: