GNOME Bugzilla – Bug 334591
CORREL provides interesting answers given blanks in input
Last modified: 2006-03-15 15:30:26 UTC
A1= 1 B1= 1 A2= B2= 2 A3= 3 B3= 3 A4= na B4= na then =correl(A1:A3,B1:B3) yields 0.65... and =correl(A1:A4,B1:B4) yields 0.73... when the documentation seems to indicate that it should give 1. ------------------------------------------- A1= 1 B1= 1 A2= na B2= 2 A3= 3 B3= 3 A4= na B4= na then =correl(A1:A3,B1:B3) yields 1... and =correl(A1:A4,B1:B4) yields 1... this seems right but is inconsistent with the above results. ------------------------------------------- A1= 1 B1= 1 A2= na B2= 2 A3= 3 B3= 3 A4= na B4= 2 then =correl(A1:A4,B1:B4) yields -0.3333 and I have no idea were that number comse from. I have put the severity at major because some people might even use the correlation function.
Created attachment 61260 [details] xls with the test cases above XL XP generates #NA! for all accept the first case which does indeed generate 1.
Andreas: what did you mean by "na"? Both Jody and I see NAs propagating as expected. Andreas: it was doing the blank-to-zero translation. Fixed in cvs.
I guess na was a bad choice. I just meant a random string as one may use to mark missing values: so just type "na" in the cell, or "x" or any other non-interpreted string. If you only fixed the "blank-to-zero" translation then it still seems to do something funny with arbitrary strings.
1 1 2 2 3 1 x x 0.632455532033676 = correl(A1:A4;B1:B4) Rather than 0.63 one should see 0. This is apparently the result of gnumeric replacing the x with 0. (I can't check until tomorrow whether your fix also fixed this issue.)
That's still funny.
fixed in cvs.