I used Wilcoxon signed-rank test in SPSS and found it returns different sums of ranks compared to what RANK.AVG returns.
Excel gives me the sums 199.5 and 76. 5
In SPSS the sums of ranks (means too) are different.
I made sure the data is the same. Anyone has an explanation?
EDIT
As mentioned by Tom, the reason seems to be the rank column. RANK.AVG gives different ranks for the same numbers in my sheet. Google returns some results on this but nothing that helped me. For example, see the ranks for the 0.2 values below. But it also happens for other values. I started from scratch with a new Excel sheet, typing everything and not copying. But the problem persists.
| supporting | opposing | diff | abs diff | rank |
|---|---|---|---|---|
| 3.6 | 3.6 | 0 | ||
| 3.6 | 3.4 | 0.2 | 0.2 | 4.5 |
| 3.8 | 4.2 | -0.4 | 0.4 | 12 |
| 4.6 | 4.2 | 0.4 | 0.4 | 7 |
| 1.6 | 4.6 | -3 | 3 | 22 |
| 4 | 3.8 | 0.2 | 0.2 | 4.5 |
| 3.8 | 4.6 | -0.8 | 0.8 | 16.5 |
| 4.6 | 4.8 | -0.2 | 0.2 | 4.5 |
| 4.8 | 4.2 | 0.6 | 0.6 | 14 |
| 2.2 | 2.4 | -0.2 | 0.2 | 2 |
| 5 | 4.6 | 0.4 | 0.4 | 12 |
| 4.2 | 2.8 | 1.4 | 1.4 | 18 |
| 5 | 3 | 2 | 2 | 20 |
| 3.6 | 3.2 | 0.4 | 0.4 | 9 |
| 4.4 | 2.8 | 1.6 | 1.6 | 19 |
| 5 | 5 | 0 | ||
| 4.2 | 3.8 | 0.4 | 0.4 | 12 |
| 3.8 | 4.4 | -0.6 | 0.6 | 15 |
| 4.6 | 4.4 | 0.2 | 0.2 | 1 |
| 5 | 1 | 4 | 4 | 23 |
| 4.6 | 4.6 | 0 | ||
| 3.4 | 3 | 0.4 | 0.4 | 9 |
| 3.8 | 4 | -0.2 | 0.2 | 4.5 |
| 4 | 3.6 | 0.4 | 0.4 | 9 |
| 4 | 3.2 | 0.8 | 0.8 | 16.5 |
| 4.8 | 2 | 2.8 | 2.8 | 21 |
CodePudding user response：
There's something going wrong in the ranks column, e.g. 0.2 starts off with a rank of 4.5 and further down it has a rank of 1 - the same number can't have a different rank.
This is what I get for the ranks
The sums are reversed wrt to SPSS, but it would depend which way round you've entered the data.
Rounding errors. So put
=ROUND(Q5-R5,1)
when calculating the differences, and it's fine.