large calculation

[Kanchan]

Dear All,
i have to calculate statistical parameters like mean error , bias, corrleation etc of about 5000 sets of data.
Can you suggest me/ guide me for the program that help to do so.
I am poor in programming
thanks
kn

 

[mirabeau]

Dear All,
i have to calculate statistical parameters like mean error , bias, corrleation etc of about 5000 sets of data.
Can you suggest me/ guide me for the program that help to do so.
I am poor in programming
thanks
kn

Bias is not a statistical parameter, it's a statistical property. In general you don't calculate it.

With mean error you should be more specific about just what you mean. Is is the standard deviation of each of your 5000 sets of data that you refer to? Standard deviation is easy enough to calculate using built-in excel functions. It is also generated as part of the correlation calculations with the VBA code below.

Correlations between each of 5000 sets of data is maybe a bit harder, and I will give you some code for this.

To check how this code runs, first rename two sheets of a new Excel workbook as "base" and "correlmatrix". Then run the testdata() code which will give you 5000 columns of data, each 20 cells long on your "base" sheet

Sub testdata()
Dim n As Long, m As Integer
n = 20: m = 5000
Sheets("base").Activate
ActiveSheet.UsedRange.ClearContents
ActiveSheet.UsedRange.Cells.Interior.Color = xlNone
With Range("A3").Resize(n, m)
    .Formula = "=int(rand()*50)"
    .Value = .Value
End With
End Sub

Now run the correlationz() code, whence your correlations should appear on the "correlmatrix" sheet after about 22 seconds, or at least that's how long it takes on my i7 computer.

Sub correlationz()
Dim t As Single
t = Timer
Dim n As Long, m As Integer, u As Integer
Dim z() As Single, y() As Single
Dim v, k As Single, q As Single, s As Integer
Dim stp As Integer, i As Long, j As Integer
Sheets("base").Activate
n = [a3].End(xlDown).Row - 2
m = [a3].End(xlToRight).Column
If m > 300 Then stp = 200 Else stp = Int(m / 2) + 1
If m > 10000 Then stp = 100
ReDim z(1 To n, 1 To m), y(1 To stp, 1 To n)
q = (n - 1) ^ 0.5
With Application
For j = 1 To m
    v = Cells(3, j).Resize(n)
    If .StDev(v) > 0 Then k = .StDev(v) * q Else k = 1
    v = .Standardize(v, .Average(v), k)
For i = 1 To n
    z(i, j) = v(i, 1)
    If j Mod stp = 0 Or j = m Then
        u = j
        For s = 1 To stp
            y(s, i) = z(i, u - stp + s)
        Next s
    End If
Next i
If j = u Then
    Sheets("correlmatrix").Cells(u - stp + 2, 2). _
        Resize(stp, u) = .MMult(y, z)
    For s = 1 To stp
    Sheets("correlmatrix").Cells(2, u - stp + s + 2). _
        Resize(u - stp + s).ClearContents
    Next s
End If
Next j
End With
MsgBox "Code took " & Format(Timer - t, "0.000") & " secs"
End Sub

Problems this size may be pushing the limits of Excel somewhat, although you can extend (maybe with some modification) the correlationz() code to correlate up to about 13,000 columns of data.

You may be better off checking out some of the good, dedicated statistical programs that are available.