After an evaluation, GNOME has moved from Bugzilla to GitLab. Learn more about GitLab.
No new issues can be reported in GNOME Bugzilla anymore.
To report an issue in a GNOME project, go to GNOME GitLab.
Do not go to GNOME Gitlab for: Bluefish, Doxygen, GnuCash, GStreamer, java-gnome, LDTP, NetworkManager, Tomboy.
Bug 334591 - CORREL provides interesting answers given blanks in input
CORREL provides interesting answers given blanks in input
Status: RESOLVED FIXED
Product: Gnumeric
Classification: Applications
Component: Analytics
git master
Other All
: Normal major
: ---
Assigned To: Morten Welinder
Jody Goldberg
Depends on:
Blocks:
 
 
Reported: 2006-03-14 23:32 UTC by Andreas J. Guelzow
Modified: 2006-03-15 15:30 UTC
See Also:
GNOME target: ---
GNOME version: ---


Attachments
xls with the test cases above (13.50 KB, application/octet-stream)
2006-03-15 02:01 UTC, Jody Goldberg
Details

Description Andreas J. Guelzow 2006-03-14 23:32:30 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.
Comment 1 Jody Goldberg 2006-03-15 02:01:10 UTC
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.
Comment 2 Morten Welinder 2006-03-15 02:28:53 UTC
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.
Comment 3 Andreas J. Guelzow 2006-03-15 04:27:53 UTC
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.
Comment 4 Andreas J. Guelzow 2006-03-15 04:42:50 UTC
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.)  
Comment 5 Morten Welinder 2006-03-15 15:19:27 UTC
That's still funny.
Comment 6 Morten Welinder 2006-03-15 15:30:26 UTC
fixed in cvs.