Hey guys,

I wanted to share some new code with you. The code below allows a user to specify a range of data and then the code will output the standardized values (mean=0 and standard deviation 1) for each of the columns. This can be a big time saver over Excel’s standardize function, which requires the user to input the mean and standard deviation and only standardizes one cell at a time. Also, this allows the user to specify specifically how they want the standard deviation calculated.

I hope that you guys enjoy the code. I welcome any feedback!

Thanks,

```Sub standardize_range()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'This code allows a user to select a group of data organized by columns and it will provide a standardized
'output with mean 0, variance 1
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim variables As Range
Dim change As Range
Set variables = Application.InputBox("Select the row of variable names:", Default:=Range("a1:c1").Address, Type:=8)
Set change = Application.InputBox("Select the row of cells containing the data to standardize:", Default:=Range("a2:c14").Address, Type:=8)
Dim Total, Total_sq, Average, Variance As Double

numRow = change.Rows.Count
numCol = change.Columns.Count
'Debug.Print "numrow=" & numrow & " numcol=" & numCol

'1. Calculate Average and Std for all Columns
'Note: This calculation for Variance and StdDev differs from Excel's slightly

For j = 1 To numCol

ReDim col(numRow) As Double
Total = 0
Total_sq = 0

For i = 1 To numRow
col(i) = change.Cells(i, j)
'Debug.Print col(i)
Total = Total + col(i)
Total_sq = Total_sq + col(i) ^ 2
Next i

Average = Total / numRow
Variance = (Total_sq / numRow) - (Average) ^ 2
Std = Variance ^ (1 / 2)
'Debug.Print "Total="; Total & " Average="; Average & " Total_sq=" & Total_sq & " Variance=" & Variance _
'& " Std=" & Std

'Store values
change.Cells(numRow + 1, j).Value = Average
change.Cells(numRow + 2, j).Value = Variance
change.Cells(numRow + 3, j).Value = Std

Next j

'2. Create Sheet to store standardized values
Sheets("Standardized_Values").Delete

'Write variable names
For k = 1 To numCol
Sheets("Standardized_Values").Cells(1, k).Value = variables.Cells(1, k) & "_std"
Next k

'3. Compute standardized values
For n = 1 To numCol

For m = 1 To numRow

Sheets("Standardized_Values").Cells(m + 1, n).Value = (change.Cells(m, n).Value - change.Cells(numRow + 1, n).Value) / _
change.Cells(numRow + 3, n).Value
Next m
Next n

End Sub```