Department Satisfaction Score Rank
Sales 12.3 11
Sales 19.7 22
Operations 14.9 15
Sales 8.5 5
Sales 14.1 14
Sales 22.4 28
Operations 24.6 30
Sales 6.3 2
Sales 21 26
Operations 9.3 7
Sales 7.5 4
Sales 10 9
Sales 15.6 17
Sales 9.8 8
Sales 20.2 24
Sales 18.4 21
Operations 13.2 13
Operations 5.8 1
Operations 19.9 23
Operations 7.1 3
Sales 11.9 10
Operations 16.5 18
Operations 22.3 27
Operations 12.5 12
Sales 17.8 19
Operations 15.4 16
Operations 8.6 6
Operations 23.5 29
Operations 20.7 25
Operations 18.2 20
Compute the rank using =rank.avg(B5,$B$5:$B$34,1)
Create the Rank Sums
Sales: 220
Operations: 245
=SUMIF(A6:A36, "Sales", C6:C35)
Use =sumif(a6:a36, "Sales", c6:c35)
Sales will be our rank sum W.
Mean of W 112.5
Std Dev of W 16.79
Apply the continuity correction factor of .5 to our rank sum W.
Lowest Corrected W: 219.5 =220-0.5
Compute the z using the lowest corrected W:
z 6.372840977 =(K18-K13)/K14
Compute the p-value: 1.85558E-10 =2*(1-NORM.S.DIST(K22,TRUE))
z = z value minus mean divided by standard deviation
This would be 2* 1-norm.s.dist(z value, TRUE)