Non parametric test in statistics by excel

[tamoghna9]

Hello excel-statistics,

Please help me how I can I do non parametric statistics by excel such as Kruskal-Wallis one-way analysis of variance,Mann–Whitney U or Wilcoxon rank sum test etc.

I don't find any built in functions or stat tools to accomplish these tests.

Sending a dependable web link also will be fine for me.

This is my first question in message board

 

[mirabeau]

There's a number of OK statistical add ins to Excel that will do these jobs if you want to pay for them.
e.g. http://www.statistixl.com/ etc.

 

[tamoghna9]

Thanks a lot mirabeau.

I am a student and I wanted these tests to be done exclusively for my thesis work .
Any free web links are available?

Or any books could you please suggest.

tamoghna

 

[Aladin Akyurek]

Thanks a lot mirabeau.

I am a student and I wanted these tests to be done exclusively for my thesis work .
Any free web links are available?

Or any books could you please suggest.

tamoghna

There are possibly some workouts like this one Spearman Rho..

http://www.mrexcel.com/forum/showthread.php?t=44080

which you could collect toegether using Google.

"Nonparametric Statistics" by S. Siegel, published in 1956, is my favorite.

 

[mirabeau]

I am a student and I wanted these tests to be done exclusively for my thesis work .
Any free web links are available?

Or any books could you please suggest.

tamoghna

I'm afraid I can't be of much positive help there.

People who write statistical stuff for the web generally want to make a bit of money from doing so. There is some free stuff around, although maybe not all that you want, and you might use Google to maybe track down some of it.

If it's for a thesis, your supervisor should be able to make recommendations better than can I, and hopefully you can access your institution's library which should have books in your area of interest.

Excel has some useful built-in functions and a couple of free statistical add-ins, but I don't know of any on the lines you need.

 

[tamoghna9]

Thanks a lot Aladin and mirabeau.

 

[P.Holko]

Hi, some time ago I've the same problem with solutions: buy program or write it yourself.
I've chosen the other. Just assign MannWhineyUtest procedure to button and enjoy.

If you want to compute other nonparametric tests see "Handbook of Parametric and Nonparametric Statistical Procedures" by Sheskin David J.

Option Base 1

Sub MannWhineyUtest()

Rem computes MannWhineyU test with normal approximation

Dim i, j, k, n, n1, n2, n3, n4, nT1, nT2 As Integer
Dim sumR1, sumR2, U1, U2, U, zScore As Double

Dim Arr1() As Variant
Dim Arr2() As Variant
Dim totalData() As Double
Dim PosR() As Variant
Dim Lwyst() As Variant
Dim PosRadj() As Double

On Error Resume Next

Set RangeN1 = Application.InputBox(Prompt:="Select one-dimensional range with sample 1", _
    Title:="Sample 1", Type:=8)

If (RangeN1 Is Nothing) Then
    MsgBox "Error no data in 'sample 1'."
    Exit Sub
End If

Arr1 = RangeN1.Value

n1 = UBound(Arr1, 1)
n2 = UBound(Arr1, 2)

Set RangeN2 = Application.InputBox(Prompt:="Select one-dimensional range with sample 2", _
    Title:="Sample 1", Type:=8)

If (RangeN2 Is Nothing) Then
    MsgBox "Error no data in 'sample 2'."
    Exit Sub
End If

Arr2 = RangeN2.Value
n3 = UBound(Arr2, 1)
n4 = UBound(Arr2, 2)

n = n1 * n2 + n3 * n4

ReDim totalData(n)
ReDim PosR(n)
ReDim Lwyst(n)
ReDim PosRadj(n)

For i = 1 To n1
    For j = 1 To n2
        totalData((i - 1) * n2 + j) = Arr1(i, j)
    Next j
Next i
For i = 1 To n3
    For j = 1 To n4
        totalData(n1 * n2 + (i - 1) * n4 + j) = Arr2(i, j)
    Next j
Next i

PosR = rank_it(totalData)

For i = 1 To n
    For j = 1 To n
        If PosR(i) = PosR(j) Then
            Lwyst(i) = Lwyst(i) + 1
        End If
    Next j
Next i

For i = 1 To n
    PosRadj(i) = (PosR(i) + (PosR(i) + Lwyst(i) - 1)) / 2
Next i

sumR1 = 0
For i = 1 To n1 * n2
    sumR1 = sumR1 + PosRadj(i)
Next i

sumR2 = 0
For i = n1 * n2 + 1 To n
    sumR2 = sumR2 + PosRadj(i)
Next i

nT1 = n1 * n2
nT2 = n3 * n4

U1 = nT1 * nT2 + nT1 * (nT1 + 1) / 2 - sumR1
U2 = nT1 * nT2 + nT2 * (nT2 + 1) / 2 - sumR2

U = WorksheetFunction.Min(U1, U2)

zScore = (U - nT1 * nT2 / 2) / (((nT1 * nT2 * (nT1 + nT2 + 1)) / 12) ^ 0.5)

If Round(WorksheetFunction.NormSDist(zScore) * 2, 4) < 0.0001 Then
    pValue = "P < 0,0001"
Else
    pValue = "P = " & Round(WorksheetFunction.NormSDist(zScore) * 2, 4)
End If

MsgBox pValue & vbCrLf & "U = " & Round(U, 4)

End Sub

Private Function rank_it(arr() As Double)
n = UBound(arr) - LBound(arr) + 1
ReDim V(n)
For i = 1 To n
    For j = 1 To n
        If arr(i) = WorksheetFunction.Small(arr, j) Then
            V(i) = j
            Exit For
        End If
    Next j
Next i
rank_it = V
End Function