Using an array to manipulate and standardize data

[Waimea]

Hi, I am working with tables in Excel and I am trying to automatically load the multicolumn table into an array,

I then want to calculate the AVERAGE for each table list column, the STDEV.P for each table list column and finally STANDARDIZE the data using the AVERAGE and STDEV.P I calculated earlier.

I think I understand how to load the table into an array but now how to actually loop through the data and manipulate it?

How can I loop through all list columns and calculate the AVERAGE for that list column, calculate the STDEV.P for that list column and finally calculate the STANARDIZE score?

Sub ArrayToStandardize()


Dim TableToArray As ListObject
Dim NewArray As Variant
Dim x As Long
Dim R As Long


ReDim NewArray(1 To 3, 1 To 100)


Set TableToArray = Sheets("Test1").ListObjects("Table1") 'Set path for Table variable
NewArray = TableToArray.DataBodyRange 'Create Array List from Table
For x = LBound(NewArray) To UBound(NewArray)
    
    NewArray(x, 1) = WorksheetFunction.Average(x, 1)
           
    'NewArray(x, 2) = WorksheetFunction.Average(x, 2)
    
    Debug.Print NewArray(x, 2)
    'Debug.Print NewArray(x, 2)
    'Debug.Print NewArray(x, 3) 
Next x
        
R = Cells(Rows.Count, 1).End(xlUp).Row + 1
Sheets("Test1").Cells(2, 5).Resize(UBound(NewArray, 1), UBound(NewArray, 2)) = NewArray
  
End Sub

 

[Waimea]

Hi,

I am still trying to automate the process of standardizing my data, I have it working manually but I want to learn how to automate a complex task like this with several calculations.

I want to calculate the average of each column, calculate the standard deviation of each column and then standardize each cell in every column with the average and the standard deviation.

How can I achieve this with or without arrays?

All help is very much appreciated!

 

[Waimea]

Can anyone help me with this? How can I iterate through columns and taking the average of that column and saving the average into a variable and then writing the variable to the worksheet?