PDA

View Full Version : Solved: Custom Array function for calculating variance



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

Bob Phillips
03-02-2010, 02:20 AM
How about this?



Function calcvariance(pInput) As Double
' declaring variables
Dim ctr As Integer
Dim Sums As Double
Dim avg As Double
Dim sumsqrdev As Double
Dim arr As Variant

If TypeName(pInput) = "Range" Then

arr = pInput
arr = Application.Transpose(arr)
ElseIf TypeName(pInput) = "Variant()" Then

arr = pInput
Else

calcvariance = CVErr(xlErrValue)
Exit Function
End If
' Initializing variables
Sums = 0
avg = 0
sumsqrdev = 0

' Counter to calculate average of the selected array
For ctr = LBound(arr) 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

Bob Phillips
03-02-2010, 02:20 AM
How about this?



Function calcvariance(pInput) As Double
' declaring variables
Dim ctr As Integer
Dim Sums As Double
Dim avg As Double
Dim sumsqrdev As Double
Dim arr As Variant

If TypeName(pInput) = "Range" Then

arr = pInput
arr = Application.Transpose(arr)
ElseIf TypeName(pInput) = "Variant()" Then

arr = pInput
Else

calcvariance = CVErr(xlErrValue)
Exit Function
End If
' Initializing variables
Sums = 0
avg = 0
sumsqrdev = 0

' Counter to calculate average of the selected array
For ctr = LBound(arr) 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

anandbohra
03-02-2010, 02:37 AM
Thanks xld :bow:
its working if I select the range but giving me error when I type values
e.g. =calcvariance(A1:a5) working
but =calvariance (5,10,15) not working #Value!

pl check

Bob Phillips
03-02-2010, 02:49 AM
Try

=calcvariance({5,10,15})

anandbohra
03-02-2010, 05:38 AM
Perfect.:thumb Thanks