[Excel] - implementing Mann-Whitney with Excel only formulas

[gianmarco]

Hi!

I am playing with implementing some statistics in Excel, with no macros, only formulas.

I have implemented, among other things, Mann-Whitney test following the procedure described in this very useful site (http://faculty.vassar.edu/lowry/webtext.html).

In essence, given two samples, the implementation I made in Excel calculates:
a) the U value
b) the z-score
c) the significance (p) (two-tailed) (applying the function: norm.distrib.st(z-score value)*2.

Cross-checking the test's results with the ones from other statistical programs, I noted that sometimes p is not correct.
That is, I noted that if norm.distrib.st(z-score value) is > 0,5, to get the right p I have to use (1-distrib.norm.st(z-score))*2.

Can anyone help me to understand this issue? It seems to me that I am missing something in there.

Thanks
Regards
Gm

 

[mp83]

Get it!

> http://www.geo.wvu.edu/~wilson/qmeth/lect8/MannWhitney.xls

 

[nsaad233]

Re: implementing Mann-Whitney with Excel only formulas

Hi,

to choose which formula you have to use to calculate the p-value (formula 1: 2*NORMDIST(z-score)) or formula 2: 2*(1-NORMDIST(z-score))), you have to look at the sample mean. If the sample mean is below the hypothesized mean, use the 1st formula, if the sample mean is above the hypothesized mean, use the 2nd formula.

Hope it will help you!