Fixed bug: Wilcoxon test with fractional data can give the wrong P value
Bottom line
When the data entered have digits after the decimal place (are not all integers), Prism (and other programs) can sometimes calculate the wrong P value for the Wilcoxon matched pairs test. This bug affects Prism 3 and later, up to 7.01 and 7.0a when the absolute value of some of the paired differences are the same (tied). We have fixed it for 7.02 (Windows) and 7.0b (Mac). You can avoid the problem by transforming all the values to larger values that do not have any digits after the decimal place.
Background
Here is a simple example. Because all of the data are integers, there is no problem.
| Before | After | Difference | abs(Difference) | Rank | Signed rank |
| 11 | 12 | 1 | 1 | 1.5 | 1.5 |
| 15 | 14 | -1 | 1 | 1.5 | -1.5 |
| 36 | 45 | 9 | 9 | 5 | 5 |
| 23 | 35 | 12 | 12 | 7 | 7 |
| 37 | 22 | -15 | 15 | 8 | -8 |
| 16 | 24 | 8 | 8 | 4 | 4 |
| 19 | 29 | 10 | 10 | 6 | 6 |
| 7 | 11 | 4 | 4 | 3 | 3 |
| Sum of positive ranks | 26.5 | ||||
| Sum of negative ranks | -9.5 | ||||
| P value | 0.2565 | ||||
The first two columns show the data -- what you would enter into a Column data table in Prism. The rest of the table shows the calculations Prism does.
- Compute the difference (After - Before) for each pair. You can ask Prism to report these values in the Options tab of the t test (etc.) dialog.
- Compute the absolute value of those differences.
- Rank those absolute values. There are several ways to deal with ties when ranking. Prism (and all programs) give the tied values the average of the ranks they would receive. The first two rows each have a difference of 1 (one an increase and one a decrease, but only the magnitude matters here). Since these two rows tie for having the smallest rank, they each get the average of 1 and 2, so are assigned a rank of 1.5.
- Compute the signed ranks. For the pairs where there was a decrease, the rank is multiplied by -1.
- Sum the positive ranks and sum the negative ranks .
- Compute the P value from those two values (and the number of ties). Prism (unlike some programs) can compute an exact P value (not an approximate one) even in the face of ties (so long as the sample size isn't huge).
The bug in Prism 7
Now let's repeat the analysis, but divide all the values by 100. This is essentially just changing the units, so should not change the ranks or the P value. But it can...
Here is what Prism 7 does.
| Before | After | Rounded Difference | Difference | abs(Difference) | Rank | Signed rank |
| 0.11 | 0.12 | 0.01 | 1.00000000000000E-02 | 1.00000000000000E-02 | 2 | 2 |
| 0.15 | 0.14 | -0.01 | -9.99999999999998E-03 | 9.99999999999998E-03 | 1 | -1 |
| 0.36 | 0.45 | 0.09 | 9.00000000000000E-02 | 9.00000000000000E-02 | 5 | 5 |
| 0.23 | 0.35 | 0.12 | 1.20000000000000E-01 | 1.20000000000000E-01 | 7 | 7 |
| 0.37 | 0.22 | -0.15 | -1.50000000000000E-01 | 1.50000000000000E-01 | 8 | -8 |
| 0.16 | 0.24 | 0.08 | 8.00000000000000E-02 | 8.00000000000000E-02 | 4 | 4 |
| 0.19 | 0.29 | 0.1 | 1.00000000000000E-01 | 1.00000000000000E-01 | 6 | 6 |
| 0.07 | 0.11 | 0.04 | 4.00000000000000E-02 | 4.00000000000000E-02 | 3 | 3 |
| Sum of positive ranks | 27 | |||||
| Sum of negative ranks | -9 | |||||
| P value | 0.2500 | |||||
If you look at the differences reported by Prism (labeled "rounded differences" above), there are no problems. You can ask Prism to show more decimal places by going to the table of differences (which you need to ask for in the Options tab), selecting the entire column of differences, clicking the button to change decimal formatting, and choosing the maximum number of digits (14 for Prism 7, 6 for Prism 6) and to turn on scientific notation always.
Now you can see that the difference in the first two rows are not identical (except for sign). Due to conversion from decimal to binary and back to decimal, the second difference is not shown as -0.01 but rather as -0.00999999999999998 which is shown above as -9.99999999999998E-03. More. When Prism ranks the absolute values of the differences, one is 0.01 and another of 9.99999999999998E-03. The two are not equal, so aren't considered ties. So the sum of positive ranks and the sum of negative ranks are wrong, as is the P value.
The bug in Prism 6
Prism 6 only stores entered data to single precision (while Prism 7 uses double precision). The same problem has a different result in Prism 6. Again a round-off error makes the differences in rows 1 and 2 not be equal, but now it is row 1 that suffers the round off error. So the signed ranks are different, as is the P value.
| Before | After | Rounded Difference | Difference | abs(Difference) | Rank | Signed rank |
| 0.11 | 0.12 | 0.01 | 9.9999980E-03 | 9.9999980E-03 | 1 | 1 |
| 0.15 | 0.14 | -0.01 | -1.0000010E-02 | 1.0000010E-02 | 2 | -2 |
| 0.36 | 0.45 | 0.09 | 8.9999970E-02 | 8.9999970E-02 | 5 | 5 |
| 0.23 | 0.35 | 0.12 | 1.2000000E-01 | 1.2000000E-01 | 7 | 7 |
| 0.37 | 0.22 | -0.15 | -1.5000000E-01 | 1.5000000E-01 | 8 | -8 |
| 0.16 | 0.24 | 0.08 | 8.0000000E-02 | 8.0000000E-02 | 4 | 4 |
| 0.19 | 0.29 | 0.1 | 9.9999990E-02 | 9.9999990E-02 | 6 | 6 |
| 0.07 | 0.11 | 0.04 | 4.0000000E-02 | 4.0000000E-02 | 3 | 3 |
| Sum of positive ranks | 26 | |||||
| Sum of negative ranks | -10 | |||||
| P value | 0.3125 | |||||
WORKAROUND
If you use Prism 7, update to 7.02 (Windows) or 7.0b (Mac) or later. Then go to a results page, drop the Change menu, and choose Recalc all.
If you use Prism 6 or must user earlier releases of Prism 7:
- Transform all the values using a user-defined transform like this (explanation):
Y=floor(10^K*Y + 0.5) - Set K to an appropriate value. Set K to 2 if you want to multiply all values by 100, because there were 2 digits after the decimal. Set K to 3 if you want to multiply all values by 1000 because there were 3 digits after the decimal.
- The results of this transform will be entirely integers, with no possibility of round-off errors.
- Run the Wilcoxon matched pairs test from the transform results.
- To confirm that there are no round-off issues, check the option in the dialog to report the set of differences, and then tell Prism to report those differences with as many digits as possible (as shown above). All the differences should be integers, with all digits after the decimal point equal to zero.
Q and A
Q. Which versions of Prism does this affect?
A. It affects Prism 3.0 up to 7.01 (Windows) and 7.0a (Mac). It is fixed in 7.02 (Windows) and 7.0b (Mac).
Q. How commonly does this problem occur?
A. It can only happen with data entered as fractional values (digits beyond the decimal point), when there are ties among the absolute values of the differences, and when those ties are affected by round-off problems (not all are). So the bug won't affect most results.
Q. Is this a problem only in GraphPad Prism?
A. No. Here is an article from 2003 showing the same problem in SAS (and giving a detailed explanation of the problem).
Q. How can I know if my results are wrong?
A. See the workaround section above.
Q. How is it fixed for Prism 7.02 and 7.0b?
A. When ranking the absolute value of the differences, Prism won't pay attention to tiny differences in the least significant digit. With Prism will think that 0.01 and 0.00999999999999998 are the same value, so tie for the same ranks.
Q. Will the bug always produce errors in both the sums of signed ranks and the P value?
A. No. If rows 1 and 2 in the example above both went in the same direction (both increases or both decreases), the sum of signed ranks would not be affected. But the P value is computed not only from those two sums but also from the number of ties. So the P value is can be wrong even when the sum of signed ranks are correct.
Q. Does the bug consistently make the P value too high, too low, or can it go either way?
A. The reported P value, when wrong, was always too low.
Q. How big is the discrepancy in P values?
A. For analyses of numbers with digits after the decimal point with ties in the differences, the magnitude of the bug depends on how many pairs there are, how many of those differences are tied, and how many of those tied differences are affected by round-off issues.
Q. Does this bug also affect the paired t test?
A. No. The tiny discrepancy in the differences due to round-off issues doesn't matter for the t test calculations. It only matters when you rank those differences (or their absolute values).
Q. Does the bug affect any other test?
A. No other nonparametric test in Prism is affected by this problem. If you use the Remove Baseline analysis to subtract one column from the other, there may be round off errors in the results. But these will make only trivial (if any) differences in other analyses.
Q. Why wasn't this bug detected in testing?
A. We test our software carefully with many data sets. Most of these data sets come from text books. None demonstrated this issue.
Q. Can I download the Prism and Excel files?
A. Yes. Excel file Prism file