Macro to standardize/normalize data

[abberyfarm]

Hi there,

I have to repetitively standardize data in columns A to AZ. It is very time consuming getting the mean and standard deviation of each column and then using the formula on each column.

I was wondering if someone could help me out with a macro to do this? The number of rows varies from sheet to sheet. I suppose it would be best to put the standardized data into a new sheet or under the original sheet or whatever is easiest.

Thank you

 

[HalfAce]

What exactly do you mean by 'standardizing' the data?
Can you describe (in words) what you're doing to it?, what decisions need to be made for any of it and what (if any) criteria gets used?

Another idea might be to use the macro recorder while you manually do (at least some of) the 'standardizing' of your data. This will provide the syntax for what you're doing and we can tweak it from there.

 

[abberyfarm]

Hi,

I'm using the built in standardize formula http://office.microsoft.com/en-us/excel-help/standardize-HP005209273.aspx

The data is in columns. So I'm trying to standardize each value in the column based on the mean and standard deviation of all the data in the column.

 

[abberyfarm]

Hi there,

I recorded the macro below, for a small sample of my data, columns A-D, and it works fine. Just paste these numbers in A1.

1.14	-1.32	0.48	-0.76
2.51	-0.47	0.88	-0.17
1.26	-1.14	0.93	-0.65
0.33	-1.39	0.21	-0.67
0.00	0.00	0.00	0.00
0.00	0.00	0.00	0.00
1.15	-0.53	0.72	-0.36
1.53	-1.36	0.56	-0.61

And run this this

Sub st()
'
' st Macro
'

'
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "=AVERAGE(R[-9]C:R[-2]C)"
    Range("A10").Select
    Selection.AutoFill Destination:=Range("A10:D10"), Type:=xlFillDefault
    Range("A10:D10").Select
    Range("A11").Select
    ActiveCell.FormulaR1C1 = "=STDEVP(R[-10]C:R[-3]C)"
    Range("A11").Select
    Selection.AutoFill Destination:=Range("A11:D11"), Type:=xlFillDefault
    Range("A11:D11").Select
    Range("A13").Select
    ActiveCell.FormulaR1C1 = "=STANDARDIZE(R[-12]C,R[-3]C,R[-2]C)"
    Range("A13").Select
    ActiveCell.FormulaR1C1 = "=STANDARDIZE(R[-12]C,R10C1,R11C1)"
    Range("A13").Select
    Selection.AutoFill Destination:=Range("A13:A20"), Type:=xlFillDefault
    Range("A13:A20").Select
    Range("A13").Select
    Selection.AutoFill Destination:=Range("A13:D13"), Type:=xlFillDefault
    Range("A13:D13").Select
    Range("B13").Select
    ActiveCell.FormulaR1C1 = "=STANDARDIZE(R[-12]C,R10C2,R11C2)"
    Range("C13").Select
    ActiveCell.FormulaR1C1 = "=STANDARDIZE(R[-12]C,R10C3,R11C3)"
    Range("D13").Select
    ActiveCell.FormulaR1C1 = "=STANDARDIZE(R[-12]C,R10C4,R11C4)"
    Range("B13").Select
    Selection.AutoFill Destination:=Range("B13:B20"), Type:=xlFillDefault
    Range("B13:B20").Select
    Range("C13").Select
    Selection.AutoFill Destination:=Range("C13:C20"), Type:=xlFillDefault
    Range("C13:C20").Select
    Range("D13").Select
    Selection.AutoFill Destination:=Range("D13:D20"), Type:=xlFillDefault
    Range("D13:D20").Select
    Range("E18").Select
End Sub

How can I modify it to do this with a variable number of rows? It would need to find the last row. It would be helpful if it would then copy the new table into a new sheet?

Thanks for your help