Excel Forum

Code: Select all

Sub MeanCal()
For r = 2 To 6
    Sheets("Sheet1").Cells(r, "J").Value = Application.WorksheetFunction.SumProduct(Range("B1:I1"), Range(Cells(r, "B"), Cells(r, "I"))) / Cells(r, "A").Value
Next
End Sub

Sub SDCal()
For r = 2 To 6
    Sheets("Sheet1").Cells(r, "K").Value = Application.WorksheetFunction.**********
Next
End Sub

Code: Select all

Sub MeanCal()

Dim ArrayA As Variant
Dim ArrayC As Variant

ArrayA = Worksheets("Sheet1").Range("B1:I1").Value
ArrayC = ArrayA

Application.ScreenUpdating = False

If Sheets("Sheet1").Cells(2, 1).Value = "" Then
MsgBox "Please import data"
Else

For r = 2 To 6
    Sheets("Sheet1").Cells(r, "J").Value = Application.WorksheetFunction.SumProduct(Range("B1:I1"), Range(Cells(r, "B"), Cells(r, "I"))) / Cells(r, "A").Value

                If Sheets("Sheet1").Cells(r, 1).Value <> "" Then
                    For i = 1 To 8
                        ArrayC(1, i) = (ArrayA(1, i) - Cells(r, "J").Value) ^ 2
                    Next i
                    Sheets("Sheet1").Cells(r, "K").Value = Application.WorksheetFunction.SumProduct(ArrayC, Range(Cells(r, "B"), Cells(r, "I"))) / Cells(r, "A").Value
                End If
Next r

End If
Application.ScreenUpdating = True
End Sub