Option Explicit ' Forces declaration of variables/types


Sub standard_deviation()
' Declare all variables, as required by enabling of Option Explicit
Dim row_count As Byte, i As Byte  ' Row count equates to number of measurements (-1 if header).  If the row count might be longer than 255 you can change them to Integer or Long, dependent on your needs
Dim data_col_index As Byte, target_col_index As Byte ' Column Number that contains the data and COlumn number to populate results
Dim sum As Double, xbar As Double ' Sum of all measures and Mean, respectively
Dim squared_diff As Double, variance As Double, std_dev As Double ' Squared Difference, Variance and Standard Deviation, respectively
Dim WS As Worksheet ' Worksheet object, should be constant, hence all caps, but Objects can't be compiled as constants in VBA
Dim rng As Range ' Range/Cell object

'Dim spread As Double
' Application Logic:
' Instantiate squared difference variable as it is a running total
squared_diff = 0
' Set col_index to the column that contains data and target column as dest.  Change as needed
data_col_index = 1: target_col_index = 2
' Set the WS constant to the first sheet in workbook.  Change as needed
Set WS = ThisWorkbook.Sheets(1)
' Get row count in column(col_index).  Change col_index as needed
row_count = WS.Cells(Rows.Count, data_col_index).End(-4162).Row
' Get sum of all measures
sum = Application.WorksheetFunction.sum(WS.Columns(1))
' Mean is sum/row_count
xbar = sum / row_count
' Loop through all rows, set range object, get squared Difference
For i = 1 To row_count
    Set rng = WS.Cells(i, data_col_index) ' Sets range object
    'spread = (rng.Value - xbar) ^ 2
    'Debug.Print CDbl(spread)
    squared_diff = squared_diff + (rng.Value - xbar) ^ 2 ' (Calculates (measurement - mean) squared and adds to running total
Next i

' Calculate spread of sample
variance = squared_diff / (sum - 1) ' variance = total of all squared differences (measurement - mean)^2 / n -1
std_dev = Sqr(variance) ' standard deviation is variance squared

Debug.Print "SUM is " & sum
Debug.Print "N - 1 is " & row_count - 1
Debug.Print "XBAR is " & xbar
Debug.Print "VAR is " & variance
Debug.Print "STD_DEV is " & std_dev

WS.Cells(2, 3).Value = std_dev

End Sub

7.436566035
7.273077821
7.421956493
7.191491523
7.41702391
7.248892198
7.289012484
7.296538223
7.326332635