GNOME Bugzilla – Bug 551234
LINEST should optionally ignore incomplete data records
Last modified: 2018-05-22 13:29:02 UTC
If a value in the x or y variables range is missing, LINEST considers that value 0 rather than ignoring the data point containg that value. (This is easy to see when one looks at the degree of freedom returned in the 2nd column of the fourht row when stats=TRUE.)
Created attachment 118340 [details] screenshot showing the problem The attached image shows the value (marked by an arrow) that should be 0 (or for compatibility with XL, should be #N/A).
Sorry the previous screen shot should ave been attached to #550933
*** Bug 597069 has been marked as a duplicate of this bug. ***
Note that I just tried LINEST on Excel 2007, and things seems to have changed. Excel now returns an error in the presence of empty cells. While this is still not great it would at least remove the silent change of empty to 0.
I can't match our code with the statement "LINEST replaces missing data with 0". We seem to ignore strings and empties separately in both arguments. That, of course, doesn't make a whole lot of sense.
Nope, we are currently having: COLLECT_IGNORE_STRINGS | COLLECT_IGNORE_BOOLS Of course just replacing this with COLLECT_IGNORE_STRINGS | COLLECT_IGNORE_BOOLS | COLLECT_IGNORE_BLANKS doesn't quite do it. I am working on that.
The "replace blank cells with 0" issue has been fixed. Of course we should provide the option of excluding all incomplete records.
The patch to bug 317426 makes us go to "anything but number is error" which isn't great, but at least consistent. It should be fairly easy to improve the situation to "ignore tuples for which any item is blank" after the patch for 317426 lands.
-- GitLab Migration Automatic Message -- This bug has been migrated to GNOME's GitLab instance and has been closed from further activity. You can subscribe and participate further through the new bug through this link to our GitLab instance: https://gitlab.gnome.org/GNOME/gnumeric/issues/105.