fboehlandt
10-09-2008, 02:03 AM
Hi everyone,
I've got the following code to calculate the F-stat of ANOVA. I require the sumsquared of all the mean deviations. In total I have 2 by 2 by4 input parameters (16 in total). Thus, I have a three-dimensional array (Squareddev), which I need to add up:
Sub testanova()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")
With ws
Mean1 = .Range("B24")
Mean2 = .Range("B25")
Mean3 = .Range("C24")
Mean4 = .Range("C25")
Obs1 = .Range("B29")
Obs2 = .Range("B30")
Obs3 = .Range("C29")
Obs4 = .Range("C30")
End With
Result = ANOVA(Mean1, Mean2, Mean3, Mean4, Obs1, Obs2, Obs3, Obs4)
ws.Select
Range("F30").Select
Selection.Value = Result
End Sub
Function ANOVA(Mean1, Mean2, Mean3, Mean4, Obs1, Obs2, Obs3, Obs4)
Dim SSAB, MSAB As Double
Dim Mean_AB(1 To 4)
Dim Obs_AB(1 To 4)
Dim Mean_A(1 To 2)
Dim Mean_B(1 To 2)
Mean_AB(1) = Mean1
Mean_AB(2) = Mean2
Mean_AB(3) = Mean3
Mean_AB(4) = Mean4
Obs_AB(1) = Obs1
Obs_AB(2) = Obs2
Obs_AB(3) = Obs3
Obs_AB(4) = Obs4
Mean_A(1) = (Mean1 * Obs1 + Mean2 * Obs2) / (Obs1 + Obs2)
Mean_A(2) = (Mean3 * Obs3 + Mean4 * Obs4) / (Obs3 + Obs4)
Mean_B(1) = (Mean1 * Obs1 + Mean3 * Obs3) / (Obs1 + Obs3)
Mean_B(2) = (Mean2 * Obs2 + Mean4 * Obs4) / (Obs2 + Obs4)
Mean = (Mean1 * Obs1 + Mean2 * Obs2 + Mean3 * Obs3 + Mean4 * Obs4) / (Obs1 + Obs2 + Obs3 + Obs4)
ReDim Squareddev(1 To 4, 1 To 2, 1 To 2)
For i = 1 To 4
For j = 1 To 2
For k = 1 To 2
Squareddev(i, j, k) = (Obs_AB(i)) * (Mean_AB(i) - Mean_A(j) - Mean_B(k) + Mean) ^ 2
Next k
Next j
Next i
ANOVA = WorksheetFunction.Sum(Squareddev) <--
End Function
This code fails on the line indicated. If the variable Squareddev is redimensioned for two dimensions only (like row and col), it works. What can I do to prevent error message: 'type mismatch'? Any help is greatly appreciated...
I've got the following code to calculate the F-stat of ANOVA. I require the sumsquared of all the mean deviations. In total I have 2 by 2 by4 input parameters (16 in total). Thus, I have a three-dimensional array (Squareddev), which I need to add up:
Sub testanova()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")
With ws
Mean1 = .Range("B24")
Mean2 = .Range("B25")
Mean3 = .Range("C24")
Mean4 = .Range("C25")
Obs1 = .Range("B29")
Obs2 = .Range("B30")
Obs3 = .Range("C29")
Obs4 = .Range("C30")
End With
Result = ANOVA(Mean1, Mean2, Mean3, Mean4, Obs1, Obs2, Obs3, Obs4)
ws.Select
Range("F30").Select
Selection.Value = Result
End Sub
Function ANOVA(Mean1, Mean2, Mean3, Mean4, Obs1, Obs2, Obs3, Obs4)
Dim SSAB, MSAB As Double
Dim Mean_AB(1 To 4)
Dim Obs_AB(1 To 4)
Dim Mean_A(1 To 2)
Dim Mean_B(1 To 2)
Mean_AB(1) = Mean1
Mean_AB(2) = Mean2
Mean_AB(3) = Mean3
Mean_AB(4) = Mean4
Obs_AB(1) = Obs1
Obs_AB(2) = Obs2
Obs_AB(3) = Obs3
Obs_AB(4) = Obs4
Mean_A(1) = (Mean1 * Obs1 + Mean2 * Obs2) / (Obs1 + Obs2)
Mean_A(2) = (Mean3 * Obs3 + Mean4 * Obs4) / (Obs3 + Obs4)
Mean_B(1) = (Mean1 * Obs1 + Mean3 * Obs3) / (Obs1 + Obs3)
Mean_B(2) = (Mean2 * Obs2 + Mean4 * Obs4) / (Obs2 + Obs4)
Mean = (Mean1 * Obs1 + Mean2 * Obs2 + Mean3 * Obs3 + Mean4 * Obs4) / (Obs1 + Obs2 + Obs3 + Obs4)
ReDim Squareddev(1 To 4, 1 To 2, 1 To 2)
For i = 1 To 4
For j = 1 To 2
For k = 1 To 2
Squareddev(i, j, k) = (Obs_AB(i)) * (Mean_AB(i) - Mean_A(j) - Mean_B(k) + Mean) ^ 2
Next k
Next j
Next i
ANOVA = WorksheetFunction.Sum(Squareddev) <--
End Function
This code fails on the line indicated. If the variable Squareddev is redimensioned for two dimensions only (like row and col), it works. What can I do to prevent error message: 'type mismatch'? Any help is greatly appreciated...