back to home page

back to VBA examples section

Standard Deviation and Mean

This example is a relatively easy one. The objective is to compute the mean and the standard deviation from an array. In this example, the array is from A1 to A10 of Sheet1 of an opened Excel Workbook. This program has two Function procedures and one Sub procedure. The sub procedure reads in the numbers from the array (A1..A10), calls up the function procedures and returns the mean and standard deviation value. The mean and standand deviation formulas are shown below. Note that the standard deviation in this example is for a sample, not a population. 

Check out Median in the Bootstrap example.  Also see Skew and Kurtosis.

'***********************************************************************
'*                                                       Mean                                                  *
'***********************************************************************
Function Mean(k As Long, Arr() As Single)
     Dim Sum As Single 
     Dim i As Integer 

     Sum = 0 
     For i = 1 To k 
         Sum = Sum + Arr(i) 
     Next i
 
     Mean = Sum / k

End Function

'************************************************************************
'*                                              Standard Deviation                                    *
'************************************************************************
Function StdDev(k As Long, Arr() As Single)
     Dim i As Integer 
     Dim avg As Single, SumSq As Single
 

     avg = Mean(k, Arr) 
     For i = 1 To k 
          SumSq = SumSq + (Arr(i) - avg) ^ 2 
     Next i
 
     StdDev = Sqr(SumSq / (k - 1))

End Function

'*************************************************************************
'*                                                    Computation                                           *
'*************************************************************************
Sub compute()
     Dim Arr(10) As Single 
     Dim Average As Single 
     Dim Std_Dev As Single 

     For i = 1 To 10

          Arr(i) = Sheets("Sheet1").Cells(i, 1) 
     Next i 

     Average = Mean(10, Arr) 
     Std_Dev = StdDev(10, Arr) 
     Sheets("Sheet1").Cells(12, 1) = Average 
     Sheets("Sheet1").Cells(13, 1) = Std_Dev

End Sub

VBA Codes