{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["Wilcoxon-Matched - Example"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Wilcoxon-Matched - Example","index":0,"n_rows":64,"n_cols":18,"rows_kept":64,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"Wilcoxon signed rank test (paired)"]
["x","y","Use?","abs(x - y)","sgn(x-y)","Rank","sgn x Rank","Rank ties freq (ft)","ties corr"]
[4.8899999999999999E-2,0.40176000000000001,{"$f":"IF(OR(A4=B4,A4=\"\",B4=\"\"),\"no\",\"yes\")","v":"yes"},{"$f":"IF(C4=\"no\",\"\",ABS(A4-B4))","v":0.35286000000000001},{"$f":"IF(C4=\"no\",\"\",SIGN(A4-B4))","v":-1},{"$f":"IF(C4=\"no\",\"\",_xlfn.RANK.AVG(D4,D:D,1))","v":3},{"$f":"IF(C4=\"no\",\"\",F4*E4)","v":-3},{"$f":"IF(C4=\"no\",\"\",COUNTIF(F:F,F4))","v":1},{"$f":"IF(H4=\"\",\"\",H4^3-H4)","v":0},null,null,null,"Step 1","Remove pairs for which both variables have the same score, or missing value"]
[-0.12562000000000001,0.37985999999999998,{"$f":"IF(OR(A5=B5,A5=\"\",B5=\"\"),\"no\",\"yes\")","v":"yes"},{"$f":"IF(C5=\"no\",\"\",ABS(A5-B5))","v":0.50547999999999993},{"$f":"IF(C5=\"no\",\"\",SIGN(A5-B5))","v":-1},{"$f":"IF(C5=\"no\",\"\",_xlfn.RANK.AVG(D5,D:D,1))","v":4},{"$f":"IF(C5=\"no\",\"\",F5*E5)","v":-4},{"$f":"IF(C5=\"no\",\"\",IF(COUNTIF($F$4:F4,F5)>0,\"\",COUNTIF(F:F,F5)))","v":1},{"$f":"IF(H5=\"\",\"\",H5^3-H5)","v":0},null,null,null,null,"See column C",null,"=IF(OR(A4=B4;A4=\"\";B4=\"\");\"no\";\"yes\")"]
[-0.26396999999999998,-3.2660000000000002E-2,"yes",0.23130999999999999,-1,2,-2,{"$f":"IF(C6=\"no\",\"\",IF(COUNTIF($F$4:F5,F6)>0,\"\",COUNTIF(F:F,F6)))","v":1},0]
[3.1390000000000001E-2,0.66215999999999997,"yes",0.63076999999999994,-1,5,-5,{"$f":"IF(C7=\"no\",\"\",IF(COUNTIF($F$4:F6,F7)>0,\"\",COUNTIF(F:F,F7)))","v":1},0,null,null,null,"Step 2","Determine the absolute value of the difference between the two variables for each case"]
[0.11294999999999999,0.20136000000000001,"yes",8.8410000000000016E-2,-1,1,-1,{"$f":"IF(C8=\"no\",\"\",IF(COUNTIF($F$4:F7,F8)>0,\"\",COUNTIF(F:F,F8)))","v":1},0,null,null,null,null,"See column D",null,"=IF(C4=\"no\";\"\";ABS(A4-B4))"]
[null,null,"no","","","","",{"$f":"IF(C9=\"no\",\"\",IF(COUNTIF($F$4:F8,F9)>0,\"\",COUNTIF(F:F,F9)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C10=\"no\",\"\",IF(COUNTIF($F$4:F9,F10)>0,\"\",COUNTIF(F:F,F10)))","v":""},"",null,null,null,"Step 3","Determine the sign of the difference, i.e. a -1 if it is negative, a 1 if it is positive"]
[null,null,"no","","","","",{"$f":"IF(C11=\"no\",\"\",IF(COUNTIF($F$4:F10,F11)>0,\"\",COUNTIF(F:F,F11)))","v":""},"",null,null,null,null,"See column E",null,"=IF(C4=\"no\";\"\";SIGN(A4-B4))"]
[null,null,"no","","","","",{"$f":"IF(C12=\"no\",\"\",IF(COUNTIF($F$4:F11,F12)>0,\"\",COUNTIF(F:F,F12)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C13=\"no\",\"\",IF(COUNTIF($F$4:F12,F13)>0,\"\",COUNTIF(F:F,F13)))","v":""},"",null,null,null,"Step 4","Rank the absolute difference from low to high, using average rank for ties"]
[null,null,"no","","","","",{"$f":"IF(C14=\"no\",\"\",IF(COUNTIF($F$4:F13,F14)>0,\"\",COUNTIF(F:F,F14)))","v":""},"",null,null,null,null,"See column F",null,"=IF(C4=\"no\";\"\";RANK.AVG(D4;D:D;1))"]
[null,null,"no","","","","",{"$f":"IF(C15=\"no\",\"\",IF(COUNTIF($F$4:F14,F15)>0,\"\",COUNTIF(F:F,F15)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C16=\"no\",\"\",IF(COUNTIF($F$4:F15,F16)>0,\"\",COUNTIF(F:F,F16)))","v":""},"",null,null,null,"Step 5","Multiply the sign with the rank"]
[null,null,"no","","","","",{"$f":"IF(C17=\"no\",\"\",IF(COUNTIF($F$4:F16,F17)>0,\"\",COUNTIF(F:F,F17)))","v":""},"",null,null,null,null,"See column G",null,"=IF(C4=\"no\";\"\";F4*E4)"]
[null,null,"no","","","","",{"$f":"IF(C18=\"no\",\"\",IF(COUNTIF($F$4:F17,F18)>0,\"\",COUNTIF(F:F,F18)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C19=\"no\",\"\",IF(COUNTIF($F$4:F18,F19)>0,\"\",COUNTIF(F:F,F19)))","v":""},"",null,null,null,"Step 6","Determine the number of, the absolute sum and the average for both the negative and the positive pairs"]
[null,null,"no","","","","",{"$f":"IF(C20=\"no\",\"\",IF(COUNTIF($F$4:F19,F20)>0,\"\",COUNTIF(F:F,F20)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C21=\"no\",\"\",IF(COUNTIF($F$4:F20,F21)>0,\"\",COUNTIF(F:F,F21)))","v":""},"",null,null,null,null,null,null,"=ABS(SUMIF(G:G;\">0\"))"]
[null,null,"no","","","","",{"$f":"IF(C22=\"no\",\"\",IF(COUNTIF($F$4:F21,F22)>0,\"\",COUNTIF(F:F,F22)))","v":""},"",null,null,null,null,null,"n","Sum","Avg"]
[null,null,"no","","","","",{"$f":"IF(C23=\"no\",\"\",IF(COUNTIF($F$4:F22,F23)>0,\"\",COUNTIF(F:F,F23)))","v":""},"",null,null,null,null,"Positive",{"$f":"SUMIF(E:E,1)","v":0},{"$f":"ABS(SUMIF(G:G,\">0\"))","v":0},{"$f":"P23/O23","v":{"$error":"#DIV/0!"}}]
[null,null,"no","","","","",{"$f":"IF(C24=\"no\",\"\",IF(COUNTIF($F$4:F23,F24)>0,\"\",COUNTIF(F:F,F24)))","v":""},"",null,null,null,null,"Negative",{"$f":"ABS(SUMIF(E:E,-1))","v":5},{"$f":"ABS(SUMIF(G:G,\"<0\"))","v":15},{"$f":"P24/O24","v":3}]
[null,null,"no","","","","",{"$f":"IF(C25=\"no\",\"\",IF(COUNTIF($F$4:F24,F25)>0,\"\",COUNTIF(F:F,F25)))","v":""},"",{"$f":"MEDIAN(A:A)","v":3.1390000000000001E-2},{"$f":"MEDIAN(B:B)","v":0.37985999999999998},null,null,"Total",{"$f":"SUM(O23:O24)","v":5}]
[null,null,"no","","","","",{"$f":"IF(C26=\"no\",\"\",IF(COUNTIF($F$4:F25,F26)>0,\"\",COUNTIF(F:F,F26)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C27=\"no\",\"\",IF(COUNTIF($F$4:F26,F27)>0,\"\",COUNTIF(F:F,F27)))","v":""},"",null,null,null,"Step 7","Determine if the frequency for each unique rank. "]
[null,null,"no","","","","",{"$f":"IF(C28=\"no\",\"\",IF(COUNTIF($F$4:F27,F28)>0,\"\",COUNTIF(F:F,F28)))","v":""},"",null,null,null,null,"See column H",null,"=IF(C4=\"no\";\"\";COUNTIF(F:F;F4))"]
[null,null,"no","","","","",{"$f":"IF(C29=\"no\",\"\",IF(COUNTIF($F$4:F28,F29)>0,\"\",COUNTIF(F:F,F29)))","v":""},"",null,null,null,null,null,null,"=IF(C5=\"no\";\"\";IF(COUNTIF($F$4:F4;F5)>0;\"\";COUNTIF(F:F;F5)))"]
[null,null,"no","","","","",{"$f":"IF(C30=\"no\",\"\",IF(COUNTIF($F$4:F29,F30)>0,\"\",COUNTIF(F:F,F30)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C31=\"no\",\"\",IF(COUNTIF($F$4:F30,F31)>0,\"\",COUNTIF(F:F,F31)))","v":""},"",null,null,null,"Step 8","For each tied rank (i.e. the frequency > 1) cube the frequency and subtract it once."]
[null,null,"no","","","","",{"$f":"IF(C32=\"no\",\"\",IF(COUNTIF($F$4:F31,F32)>0,\"\",COUNTIF(F:F,F32)))","v":""},"",null,null,null,null,"See column I",null,"=IF(H4=\"\";\"\";IF(H4>1;H4^3-H4;\"\"))"]
[null,null,"no","","","","",{"$f":"IF(C33=\"no\",\"\",IF(COUNTIF($F$4:F32,F33)>0,\"\",COUNTIF(F:F,F33)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C34=\"no\",\"\",IF(COUNTIF($F$4:F33,F34)>0,\"\",COUNTIF(F:F,F34)))","v":""},"",null,null,null,"Step 9","Choose as a test statistic either the minimum of the two sums from step 6, or simply the positive"]
[null,null,"no","","","","",{"$f":"IF(C35=\"no\",\"\",IF(COUNTIF($F$4:F34,F35)>0,\"\",COUNTIF(F:F,F35)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C36=\"no\",\"\",IF(COUNTIF($F$4:F35,F36)>0,\"\",COUNTIF(F:F,F36)))","v":""},"",null,null,null,null,null,"positive","min of sum","from"]
[null,null,"no","","","","",{"$f":"IF(C37=\"no\",\"\",IF(COUNTIF($F$4:F36,F37)>0,\"\",COUNTIF(F:F,F37)))","v":""},"",null,"UDF",{"$f":"WMSRT($A$4:$A$57,$B$4:$B$57,1)","v":{"$error":"#NAME?"}},null,"Test statistic",{"$f":"P23","v":0},{"$f":"MIN(P23:P24)","v":0},{"$f":"IF(P23<P24,N23,N24)","v":"Positive"},"=IF(ABS(P23)<ABS(P24);N23;N24)"]
[null,null,"no","","","","",{"$f":"IF(C38=\"no\",\"\",IF(COUNTIF($F$4:F37,F38)>0,\"\",COUNTIF(F:F,F38)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C39=\"no\",\"\",IF(COUNTIF($F$4:F38,F39)>0,\"\",COUNTIF(F:F,F39)))","v":""},"",null,null,null,"Step 10","Determine the hypothesized sum of ranks in the population"]
[null,null,"no","","","","",{"$f":"IF(C40=\"no\",\"\",IF(COUNTIF($F$4:F39,F40)>0,\"\",COUNTIF(F:F,F40)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C41=\"no\",\"\",IF(COUNTIF($F$4:F40,F41)>0,\"\",COUNTIF(F:F,F41)))","v":""},"",null,null,null,null,"Mu T",{"$f":"O25*(O25+1)/4","v":7.5},"=O24*(O24+1)/4"]
[null,null,"no","","","","",{"$f":"IF(C42=\"no\",\"\",IF(COUNTIF($F$4:F41,F42)>0,\"\",COUNTIF(F:F,F42)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C43=\"no\",\"\",IF(COUNTIF($F$4:F42,F43)>0,\"\",COUNTIF(F:F,F43)))","v":""},"",null,null,null,"Step 11","Determine the variance"]
[null,null,"no","","","","",{"$f":"IF(C44=\"no\",\"\",IF(COUNTIF($F$4:F43,F44)>0,\"\",COUNTIF(F:F,F44)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C45=\"no\",\"\",IF(COUNTIF($F$4:F44,F45)>0,\"\",COUNTIF(F:F,F45)))","v":""},"",null,null,null,null,"Var T",{"$f":"O25*(O25+1)*(2*O25+1)/24","v":13.75},"=O24*(O24+1)*(2*O24+1)/24"]
[null,null,"no","","","","",{"$f":"IF(C46=\"no\",\"\",IF(COUNTIF($F$4:F45,F46)>0,\"\",COUNTIF(F:F,F46)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C47=\"no\",\"\",IF(COUNTIF($F$4:F46,F47)>0,\"\",COUNTIF(F:F,F47)))","v":""},"",null,null,null,"Step 12","Determine the correction for tied ranks"]
[null,null,"no","","","","",{"$f":"IF(C48=\"no\",\"\",IF(COUNTIF($F$4:F47,F48)>0,\"\",COUNTIF(F:F,F48)))","v":""},"",null,null,null,null,"Ties corr",{"$f":"SUM(I:I)","v":0}]
[null,null,"no","","","","",{"$f":"IF(C49=\"no\",\"\",IF(COUNTIF($F$4:F48,F49)>0,\"\",COUNTIF(F:F,F49)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C50=\"no\",\"\",IF(COUNTIF($F$4:F49,F50)>0,\"\",COUNTIF(F:F,F50)))","v":""},"",null,null,null,"Step 13","Determine the variance corrected for tied ranks"]
[null,null,"no","","","","",{"$f":"IF(C51=\"no\",\"\",IF(COUNTIF($F$4:F50,F51)>0,\"\",COUNTIF(F:F,F51)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C52=\"no\",\"\",IF(COUNTIF($F$4:F51,F52)>0,\"\",COUNTIF(F:F,F52)))","v":""},"",null,null,null,null,"Var T corr",{"$f":"O45-O48/48","v":13.75}]
[null,null,"no","","","","",{"$f":"IF(C53=\"no\",\"\",IF(COUNTIF($F$4:F52,F53)>0,\"\",COUNTIF(F:F,F53)))","v":""},""]
[null,null,"no","","","","",{"$f":"IF(C54=\"no\",\"\",IF(COUNTIF($F$4:F53,F54)>0,\"\",COUNTIF(F:F,F54)))","v":""},"",null,null,null,"Step 14","Determine the standard error"]
[null,null,"no","","","","",{"$f":"IF(C55=\"no\",\"\",IF(COUNTIF($F$4:F54,F55)>0,\"\",COUNTIF(F:F,F55)))","v":""},""]
[null,null,null,null,null,null,null,null,null,null,"UDF",{"$f":"WMSRT($A$4:$A$57,$B$4:$B$57,3)","v":{"$error":"#NAME?"}},null,"SE",{"$f":"SQRT(O52)","v":3.7080992435478315}]
[]
[null,null,null,null,null,null,null,null,{"$f":"IF(H58=\"\",\"\",IF(H58>1,H58^3-H58,\"\"))","v":""},null,null,null,"Step 15","Determine the z-value"]
[]
[null,null,null,null,null,null,null,null,null,null,"UDF",{"$f":"WMSRT($A$4:$A$57,$B$4:$B$57,2)","v":{"$error":"#NAME?"}},null,"Z",{"$f":"(O37-O41)/O56","v":-2.0225995873897262}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,"Step 16","Determine the significance"]
[null,null,null,null,null,null,null,null,null,null,"UDF",{"$f":"WMSRT($A$4:$A$57,$B$4:$B$57,0)","v":{"$error":"#NAME?"}},null,"sig. = p =",{"$f":"2*(1-NORMSDIST(ABS(O60)))","v":4.31144467830753E-2},"=2*(1-NORMSDIST(ABS(O60)))"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"2*(1-_xlfn.NORM.S.DIST(ABS(O60),TRUE))","v":4.31144467830753E-2},"=2*(1-NORM.S.DIST(ABS(O60);TRUE))"]