Non parametric test in statistics by excel

tamoghna9

New Member
Joined
Aug 26, 2010
Messages
6
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:)
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,530
Messages
6,179,373
Members
452,907
Latest member
Roland Deschain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top