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,

Adam

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 Application.DisplayAlerts = False Sheets("Standardized_Values").Delete Sheets.Add.Name = "Standardized_Values" '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

Advertisements