anandbohra
03-01-2010, 11:03 PM
For learning arrays I made this small function to calculate variance
but it gives me #value error & also I am not able to debug is
Function calcvariance(arr() As Double) As Double
' declaring variables
Dim ctr As Integer
Dim Sums As Double
Dim avg As Double
Dim sumsqrdev As Double
' Initializing variables
Sums = 0
avg = 0
sumsqrdev = 0
' Counter to calculate average of the selected array
For ctr = 1 To UBound(arr())
Sums = Sums + CDbl(arr(ctr))
Debug.Print Sums
Next ctr
avg = Sums / UBound(arr())
' Counter to calculate sum of squared deviations from mean
For ctr = 1 To UBound(arr())
sumsqrdev = sumsqrdev + (CDbl(arr(ctr)) - avg) ^ 2
Debug.Print sumsqrdev
Next ctr
'Finally variance = sum of squared deviations divided by n-1
calcvariance = sumsqrdev / (UBound(arr()) - 1)
End Function
any help will be highly appreciated
but it gives me #value error & also I am not able to debug is
Function calcvariance(arr() As Double) As Double
' declaring variables
Dim ctr As Integer
Dim Sums As Double
Dim avg As Double
Dim sumsqrdev As Double
' Initializing variables
Sums = 0
avg = 0
sumsqrdev = 0
' Counter to calculate average of the selected array
For ctr = 1 To UBound(arr())
Sums = Sums + CDbl(arr(ctr))
Debug.Print Sums
Next ctr
avg = Sums / UBound(arr())
' Counter to calculate sum of squared deviations from mean
For ctr = 1 To UBound(arr())
sumsqrdev = sumsqrdev + (CDbl(arr(ctr)) - avg) ^ 2
Debug.Print sumsqrdev
Next ctr
'Finally variance = sum of squared deviations divided by n-1
calcvariance = sumsqrdev / (UBound(arr()) - 1)
End Function
any help will be highly appreciated